Level of Detail Expressions in Tableau

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.

Table of Contents

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.

In this view, even if we add other dimensions like Category, the calculation of sales profit will still be fixed for the State instead of being split into two dimensions.

Suppose we have not used a fixed LOD expression. In that case, the calculations for sales profit will be displayed as shown in the image below after adding the category, which considers both dimensions.

This way, we can use the FIXED expression in Tableau calculations to fix the calculated values for a specific dimension.

INCLUDE LOD Expressions in Tableau Calculations

In this example, we will see the use case of the INCLUDE LOD expression in the Tableau calculations. Using the INCLUDE LOD adds more detail to a calculation, like including the names or values of dimensions and measures that are not in view.

For example, we want to show average sales per Product, even if the view only shows the average sales for Category.

Now, create a calculated field Average Sales using the formula below.

{INCLUDE [Product Name] : AVG([Sales])}

After this, add the Category dimension to the Rows and the calculated field to the Text card in the Marks section.

Now, in the text table, we will see the calculation for both dimensions, Category and Product Name.

In this calculation,Tableau calculates sales at the product level, then averages them back up to show one number for each Category.

In case we have just used AVG(Profit) for this view, then it will calculate values for the Category dimension only, as shown in the image below.

This way, we can use and apply the INCLUDE LOD expression in Tableau to calculate the values across the entire dataset, including dimensions not present in the view.

EXCLUDE LOD Expressions in Tableau Calculations

In this example, we will see the use case of the EXCLUDE LOD expression in the Tableau calculations. The EXCLUDE LOD does the opposite of the INCLUDE LOD. It removes specific details from the calculation and then returns the values.

For example, we have Sub-Category and Category in the view, but we want average sales per Category, ignoring the Sub-Category.

To create this view, use the formula below to calculate the average Sales for the Category excluding the Sub-Category.

{ EXCLUDE [Sub-Category] : AVG([Sales]) }

Now, drag the Category, Sub-Category to the Rows and calculated field to the text card in the Marks section.

Now, we can see the average of Sales is calculated for the Category dimension, excluding the Sub-Category.

Without using the EXCLDUE LOD, the average sales will be calculated considering both categories and subcategories, as shown in the table image below.

This way, we can use the EXCLUDE LOD expression in Tableau calculations to ignore or exclude the calculation for a specific field.

Conclusion

In this Tableau tutorial, we have learned how we can use Level of Detail (LOD) expressions in Tableau to solve calculation issues that can be solved using standard calculations. By using FIXED, INCLUDE, and EXCLUDE LOD expressions, we can control the level at which data is calculated.

Following the above examples, you can use the LOD expressions to make your Tableau table and report calculations more accurate.