LODs in Tableau: Multi-dimension breakdown and Filter Context

LODs in Tableau: Multi-dimension breakdown and Filter Context

When an average line for a chart is required in Tableau, adding a reference line is a straightforward and effective approach. However, breaking it down into multiple dimensions simultaneously can sometimes be challenging without using Level of Detail (LOD) expressions in Tableau.

Adding Average Lines by Region

For instance, if we want to add average lines for each region in a chart, we can simply drag a reference line into each pane via the Analytics pane. This method works seamlessly to display average lines for each region.

 

Adding Average Lines by Region and Year

If we aim to add average lines for each region per year, we need an additional dimension. This can be achieved using an LOD expression, such as EXCLUDE or FIXED. Below are the steps to achieve this:

1. Create a Calculated Field:
   • Use the following LOD expression:

     { EXCLUDE [Category] : AVG([Profit]) }

   • Here, “Category” is the dimension to be excluded, and “Profit” is the measure for which we want to calculate the average.
2. Add the Calculated Field to the Chart:
   • The calculated field, e.g., “Overall Profit,” can then be added to the original chart. This will display grey average lines for each region per year.

 

Alternatively, the same result can be achieved using a FIXED LOD expression. Instead of excluding the “Category” dimension, we fix the “Region” and “Year” dimensions to compute the average profit. For example:

{ FIXED [Region], [Year] : AVG([Profit]) }

 

 

Filter Context and LOD Expressions

It is important to understand the filter context and its impact on LOD expressions. Tableau has an order of operations that governs the interaction between filters and LOD calculations:

• FIXED LOD expressions are computed after context filters but before dimension filters.
• EXCLUDE and INCLUDE LOD expressions are computed after dimension filters.

 

This distinction can lead to variations in results depending on the filters applied. For example:

 

1. Using Dimension Filters:
   • A filter applied to “Category” will affect the calculation when using EXCLUDE, but not when using FIXED
     

     

2. Using Context Filters:
   • When the filter is changed to a context filter, both FIXED and EXCLUDE LOD expressions are computed after the context filter. This results in identical average calculations for both expressions.

     

 

Best Practices

Understanding LOD expressions and their interaction with filters is essential for managing complex filtering and granularity requirements in Tableau. To ensure accuracy:

• Test different LOD expressions and filter combinations.
• Be mindful of the order of operations and how filters impact your calculations.
• Use context filters judiciously to standardize the behavior of FIXED and EXCLUDE LOD expressions.

By mastering these concepts, you can create dynamic and precise visualizations tailored to your analytical needs.