For me, much of the confusion surrounding Tableau’s Level of Detail (LOD) calculations stems from the fact that they occur behind the scenes. It’s easy to grasp changes to a visualisation when the displayed granularity has been altered, but less so if only the granularity of the calculation has been altered. This behind-the-scenes aspect of LOD calculations is probably why they seemed so elusive to me the first time I had to use one. Keep reading if you want to know more about when and how to use these tricky calcs!
Why use a LOD expression?
Say you’ve created a geographical hierarchy with sales data by location. If you drag SUM(Sales) from the Superstore data set onto label (or colour, size or detail), and toggle between City and State, the information conveyed by the visualization will also toggle between lower and higher aggregation.
Say then that you want to display an aggregation at low granularity (like Country) but perform calculations at a higher granularity (like State). This would disrupt the inverse relationship between aggregation and granularity and can’t be done, right?
Enter the LOD
LOD functions
There are three functions that perform LOD calculations: Include, Exclude and Fixed. Let’s break down what each one does:
Include adds dimensions to the visualization to include HIGHER levels of granularity. The original granularity is still displayed, however Tableau is allowing the worksheet to go to a lower level of aggregation and perform calculations.
Exclude does the opposite. If you’re aiming to LOWER granularity for a certain calculation but still want your worksheet to display that original higher granularity, Tableau will exclude the higher granularity dimension for a behind-the-scene calculation.
The fixed function is used to explicitly specify at which level you want your calculation to be aggregated. Unlike Include and Exclude which are context based, a Fixed expression doesn’t consider from which visualisation you’re calling it. It will always be fixed at the level of detail specified. Fixed LOD expressions ignore filtering at the visualisation level. To get around this, right click the filter pill and choose ‘Add to Context’.
A quick Include example
Using Tableau’s built in Superstore dataset, it’s easy to drag State to the worksheet and AVG(Sales) to colour to get a filled map. Ctrl + dragging the longitude measure beside itself on columns will then duplicate the view and produce 2 marks cards.
Changing the level of granularity on the second marks card from State to City will produce circles for each city in the data set rather than filled states. Moving AVG(Sales) from colour to size and changing the right Longitude measure to a dual axis will give the following result:
Shown above is a filled map with the average of all sales within each state. Also depicted by size is the average of all sales within each city. But what if you want the average sales by state shown as well as the SUM(Sales) for each city averaged by state rather than displayed for each city?
This can be done with the Include function by specifying that the calculation will occur on the city level.
Which gives the following result when the LOD calculation replaces AVG(Sales) on the second marks card and the granularity is pushed up to state level:
Shown above is just one size indicator for each state. Each one represents the state average of SUM(sales) for each city in 2018. Note that a dual axis isn’t necessary for a visualisation if you only want to show one aggregation rather than the two that are displayed above.
The above example can also be achieved with the Fixed function:
Because fixed functions are explicit rather than relative, Country and State will also be specified in the calculation:
Replacing AVG(LoD INCLUDE City) with AVG(LoD FIXED City) and right clicking on the YEAR(Order Date) pill in filters to select ‘Add to Context’ will display the same visualisation produced above.
Want to know more?
Bora Beran explains when to use the Include, Exclude or Fixed functions and how they work in this video.
Stay tuned for Out of Sight, Out of Mind: LOD expressions (Part 2) for an example using the Exclude function!
