Tableau Level Of Detail Expressions (LOD) With Examples
Level of Detail expressions in Tableau allows us to create complex calculations. It is useful for using dimensions from different levels with aggregation. It enables us to use data from different granularities at the same time. LOD expressions can be created with this format;
There are three types of LOD expressions;
- Fixed,
- Include,
- Exclude.
FIXED
Fixed LOD enables us to compute dimensions at an independent level, even when they are not in the view.
For further examples we will use Sample-Superstore data which is a built-in dataset in Tableau.
In the Sample-Superstore dataset, we can easily find total sales for each country by using ‘Country’ and ‘SUM(Sales)’
There is only one country in the dataset. We can find the sales ratio of every state. For achieving this result, we need to create a calculated field that will always get the total sales of the country even we exclude a state or add a new dimension to the view.
In these calculations, firstly we created a calculated field that only gets total sales of country. Sales measure gets the total sales for each state. Then we created a new calculated field for the calculating percentage.
INCLUDE
This expression takes into account a dimension that is out of view. It provides a lower level of detail than the current level in the view.
In the Sample-Superstore dataset, we can calculate the average profit of each state using ‘State’ and ‘AVG(Profit)’.
If we want to take into account every city in this calculation without placing the city dimension in the view, we will need an INCLUDE LOD expression.
There is a difference between the two calculations. In ‘Avg. Profit’, the calculation is created by taking the average of all profits for each State. However, in ‘Avg. Total Profit by City’ at first, we calculate the total profit of the city by city. Then we get an average of all cities for each state.
EXCLUDE
This expression enables us to remove a dimension that is in the view. It provides a higher level of detail at the current level in the view.
In the Sample-Superstore dataset, we can get the total sales of category and subcategory dimensions.
Exclude LOD expression can display the total sales of the category dimension even subcategory remains in the view.
In this example, Tableau ignores the current level of detail and makes calculations at a higher level. Since we identify our calculated field with ‘SUM(Sales)’, the result in the ‘Total Sales Without Subcategory’ will be the summary of higher levels. Changing the aggregation from ‘ATTR’ to another one will not affect the result.
LOD expressions are useful features in Tableau. It can be easily concluded from the examples that calculations can be achieved by FIXED expressions instead of INCLUDE and EXCLUDE.
You can check this article on Tableau’s website for more examples:
https://www.tableau.com/about/blog/LOD-expressions
