LODs of fun in Tableau

In the current data-driven world, Tableau is a significant tool that provides powerful data visualization and analytics. It provides a robust set of tools and features to convert raw data into valuable insights.

Photo by Luke Chesser on Unsplash

One of the most powerful features of Tableau is the LOD (Level Of Detail) expressions. This feature helps you compute values at various granularity levels, ensuring a more accurate and flexible analysis of data.

LOD functions allow users to perform complex calculations across different levels of detail within their data. They allow users to define the level of detail for a particular calculation, regardless of the level of aggregation in their view. In other words, LOD expressions allow you to isolate specific levels of data within a visualization, independent of other filters or dimensions.

Types of LOD

1. FIXED - Used for an entirely independent level

2. EXCLUDE - Used for a less granular level

3. INCLUDE - Used for a more granular level

Syntax of the LOD expressions :

Irrespective of the type of LOD used, the syntax is the same as below:

{ TYPE of LOD [Optional Dimension List] : AGGREGATE}

Let us take a look at the types of LOD in detail.

FIXED LOD

The Fixed LOD considers only the expressions mentioned in the LOD and will be independent of other filter conditions in the view. The end result of a Fixed LOD is scalar - it returns a single value.

Let us take a look at the demonstration of FIXED LOD.

We will use the Sample - Superstore excel Dataset for this purpose.

Drag the Sales Measure to Text under the Marks card. It displays the SUM(Sales) . Let us say we want to keep this value as fixed in our view irrespective of any other dimensions or filters in the view.

In order to do that let us create a FIXED LOD expression.

Create a calculated field using the syntax as above. Double click on the created FIXED LOD in order to add it to the table view.

To understand how the FIXED LOD remains the same, despite adding other dimensions , drag the Category dimension to the Rows. Now the value displayed under Sales column in the view is Category - wise sales whereas the FIXED LOD value remains the same as before.

To further demonstrate that FIXED LOD remains unaffected by Dimension filters in the view, let us make the Category Dimension a filter.

When we make changes to the filter, the Category- wise sales changes but the FIXED LOD (Fixed Total Sales) remains constant and unaffected by the filter changes. The Sales column in the view only displays sales for Office Supplies and Technology categories but the Fixed Total Sales displays the same value as before. (See pic below)

NOTE : FIXED LODs are unaffected by Dimension Filters. But they are affected by Context Filters. So, in the current scenario if the Category filter is changed to Context by choosing 'Add to Context' under filters, the FIXED LOD value will change based on the context filter.

EXCLUDE LOD

The EXCLUDE LOD expressions let you specify a dimension or dimensions to exclude from the computation of an aggregate value.

The result of an EXCLUDE LOD expression is always an aggregate value and it changes by changes in Dimension filters.

Let us take a look at how EXCLUDE LOD expressions work.

Add Category to Rows and Sales to Text under the Marks card. Double Click on the Fixed Total Sales calculated field (Fixed LOD) to add it to the table view.

Let us create an EXCLUDE LOD using the Data Calculated Field. The above expression will exclude the Category Dimension from the Aggregate value of Sum(Sales). Therefore, now it is essentially same as the FIXED LOD.

Let us take it further by adding the Sub-Category Dimension to the view.

Take a look at the picture above. We have now modified the EXCLUDE LOD calculated field to exclude the Sub- Category Dimension from the calculation. So, the Sales column displays sub-category wise sales but the EXCLUDE LOD column shows the aggregate sales value excluding the category and sub-category dimensions break-up.

Unlike the FIXED LOD expressions, EXCLUDE LOD expressions are affected by the changes in the Dimension Filters. The picture below shows changes to the EXCLUDE LOD column based on the changes to the Category Dimension Filter.

INCLUDE LOD

The INCLUDE LOD is similar to the EXCLUDE LOD with the difference that the INCLUDE LOD expressions compute values using the specified dimensions in addition to whatever dimensions are in the view.

The result of an INCLUDE LOD expression is also an aggregate value and it changes by changes in Dimension filters.

To understand the INCLUDE LOD expressions, let us say, in addition to Category wise Sales, we want to display the average regional profit for each of the categories without actually displaying the region.

We will create an INCLUDE LOD expression including the Region Dimension for the Sum of Profit aggregate and then taking the Average of the value.

As in EXCLUDE LOD expressions, the INCLUDE LOD expressions also are affected by the change in Dimension filter selections as shown below.

This concludes a brief introduction to the types of LODs in Tableau.

With the example provided, I hope you are now equipped with a basic knowledge of how LOD calculations can be applied in Tableau visualizations.

Thanks for reading this blog!