MIP Logo

How to Have Multiple Average Lines One Chart: Fixed Level of Detail Expressions

This post was originally published on The Data School blog between 2018 and July 2025, before our program was renamed to MIP’s Analytics Career Accelerator. References throughout this article to “The Data School” or “DS” all refer to what is now MIP’s Analytics Career Accelerator. The program, its people, and its commitment to launching outstanding analytics careers remain the same – just under a new name.

If you are having trouble viewing this article, please report it here. 

Level of Detail Expression

 

When working with data and creating charts, there will come a time when you need to compute values on different levels. We call this “Level of Detail Expressions,” and the following screenshots show what we mean by LOD Expressions. In this post, I am only focusing on fixed LOD, but there are also Include and Exclude LODs. You can learn more about them at: https://help.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_lod.htm

 

 

The chart in the left screenshot has one average line across three categories (Furniture, Office Supplies, and Technology). The chart in the screenshot on the right has three average lines that represent the average value of each category. So, LOD Expressions give us more control over the level of granularity we want to compute.

 

How to Create LOD Expressions

 

The first step is to create a calculated field.

 

The formula above is fixed on [Sub-Category]: SUM[Sales] first which means that the values are SUM at the Sub-Category level first.

It is then fixed on Category: AVG which means that the level of granularity the following calculation will take is the average sales value of Sub-Categories that are under the same category.

So, if there it is not fixed on Category level as the screenshot shows,

 

it will result in just SUM Sales value at the Subcategory level.

 

 

 

 





Dual Axis


Once you created the “Category Average” calculated field, it is now time to bring it to the sheet and compare it to the existing value. You can choose a different mark type for the value but the Gantt bar is one of the good options.




 


A few more things…


Now, the LOD expression part is done, however, the LOD alone does not build the chart below. For example, the colour indicators and up&down indicators are needed. So, in future posts, I am hoping to write about how to make colour indicators and up&down indicators.




 


Hope you learned something new! If you know how to do colour indicators and up&down indicators, go to this site: https://www.workout-wednesday.com/2021w16tab/ and perhaps you can complete the challenge for yourself!


 


 


Share this post