In Tableau, I was working on the Sales reports. In this, I faced an issue where the calculations didn’t return the expected values. For example, I wanted to find the first order date for each customer, but when I applied a filter, the result didn’t change.
In another case, I needed to compare discounts across categories, but the results showed the same value for both filtered and overall results.
As a solution, I fixed this issue using the Level of Detail Expressions in Tableau. Using this, we can also include, exclude, or modify a calculation.
In this Tableau tutorial, I will explain the Level of Detail Expressions in Tableau and how we can use them to create calculations.
Tableau Level of Detail Expressions (LOD)
In Tableau, LOD (Level of Detail) expressions are used to calculate values at different levels in a data visualisation. These expressions allow for more control over how data is calculated and displayed in a visualisation.
For example, the Tableau report displays sales by Category, but we also want to view the average sales per Customer. Without LODs, this isn’t possible because Tableau is focused on categories. In this calculation using the FIXED LOD, we can calculate each customer’s average sales, regardless of the dimensions, such as Category or Region, that are in view.
Types of LODs in Tableau
In Tableau, there are three types of Level of Detail Expressions that are as follows.
- FIXED Expression: In Tableau, the FIXED expression is used to calculate at a specific level, ignoring what’s shown in the view. With this, we can specify the exact dimensions we want to include in our calculation.
It basically locks a calculation at a specific dimension and ignores other dimensions or filters unless they’re context filters.
Syntax for FIXED Expression:
{ FIXED [Dimension] : AGG([Measure]) }
- INCLUDE Expression: This expression adds extra dimensions to the calculation, making it more detailed than the current view.
For example, we can calculate average sales per customer within each region, even if the view only shows region totals. These calculations respect filters and are helpful when we need more dimensions than those already visible.
Syntax for INCLUDE Expression:
{INCLUDE [Dimension]: Calculation}
- EXCLUDE Expression: In Tableau calculations, the EXCLUDE expression is used to exclude dimensions from a calculation while still including all other dimensions in the visualisation.
For example, to calculate total sales per region while ignoring the product category dimension. These calculations are affected by filters but ignore the excluded dimensions, and they are helpful when we want to ignore unnecessary details.
Syntax for EXCLUDE expression:
{EXCLUDE [Dimension]: Calculation}
Use LOD Expressions in Tableau
In the examples below, we will see the use cases of all three LOD expressions in Tableau calculations.
FIXED LOD Expressions in Tableau Calculations
In this example, we will see how to use the Tableau FIXED LOD expression. For example, we want to find the total profit per State, by including the Category. Usually, Tableau would split profit by both State and Category.
By using the FIXED expression, we can lock the profit values for the states only.
Now, create a calculated field “State Sales Profit” using the formula below.
{ FIXED [State] : SUM([Profit]) }
After this, drag the State to Rows and Profit to the text card in the Marks section.
