Tableau LOD Expressions

1. Overview

The Tableau LOD expressions (Level of Detail) allow you to control the granularity of calculations. They enable you to perform calculations at different levels of detail than the visualization itself, extremely powerful for complex analyses.

1. What they are
2. Types of LOD
3. Syntax
4. Examples

2. LOD Expressions

The Tableau LOD Expressions allow you to control the granularity or the level of detail of your expression.

Granularity means individual distinguishable pieces.

The level of detail could mean high-level or low-level detail. High-level detail leaves out details, overall summary, less granular and more aggregated whereas the low-level detail gets down into the details, more granular and less aggregated.

3. Level of Detail in Tableau

In Tableau, the Tableau LOD Expressions – “Level of detail” is what’s shown in your view.

• What does each value represent?
• What does each mark represent?

For this article, download the superstore sample data set from Tableau.

The Level of Detail in this superstore data set represents a transaction at an order date and ship date for a specific customer, segment, country, city and state.

Let’s connect to the superstore data set.

Drag the SALES to the Marks tab:

The SUM(Sales) with no dimensions means the grand sales total of all the records. Now, let’s add a new dimension "Category".

Now, the SUM(Sales) is at the level of category. The more you add the dimensions, the more granular the number becomes. For example, if you add the “Sub Category” to the dimensions, the value changes:

Right now, the SUM(Sales) is at the level of sub-category and category (the more granular sub-total).

3.1. Types of LOD

1. FIXED (an entirely independent view) – independent of view
2. EXCLUDE (A less granular level) – minus dimensions from view
3. INCLUDE (A more granular level) – add dimensions to view (though it is not in the view)

A level of detail expression has the following structure:

{[FIXED | INCLUDE | EXCLUDE] <dimension declaration> : <aggregate expression>}

You can create Tableau LOD expressions with single or multiple dimensions. Remember, the dimension list is optional. The Aggregate expression typically could be your SUM, MIN, MAX,.. expressions.

Always build your text table that has the numbers you want. It gives you a reference like a cheat sheet and tells you all the dimensions you need in your dimension. Further, serves as a validation checkpoint for your Tableau charts, views.

4. Fixed LOD

The Fixed Tableau LOD expressions, the most commonly used LOD expression only looks at the expression for the granularity (dimension list) and the other dimensions in the view won’t affect the expression, independent of dimensions in view and the result is scalar.

Now, let’s create a calculated field with the below calculation:

{ FIXED : SUM([Sales])}

Note that we haven’t specified optional dimensions.

Let’s add discrete dimensions “Category”:

The level of detail in the measures is determined by the discrete dimensions that you have in the view. Therefore, this sum of sales in the above view is the sum of sales grouped by category. However, the fixed grand total is a fixed level of detailed expression and doesn’t care about what is in the view.

Since there is no dimension specified in the Fixed LOD calculation, this simply calculates the sum of the sales across all categories as if there are no dimensions in the view.

In the above view, the Grand total of Sales and individual fixed LOD rows are the same.

4.1. Fixed LOD filter

The dimension filters’ modifications won’t affect the Fixed LOD expressions. Next, include the Category dimensions in the filters. Altering the selected values in the category filter will not affect the Fixed LOD value.

Tableau follows a specific sequence, known as the order of operations or the query pipeline, to carry out different actions or operations. Since filters play a significant role, as you construct a view and include filters, they are always applied in the predetermined order dictated by the order of operations.

4.2. Order of operations

Take a closer look at the order of operations outlined below. The Fixed LOD is positioned between context filters and dimension filters. As a result, it remains unaffected by dimension filters but is influenced by context, data source, and extract filters.

Include the category filter in the Context and observe the outcomes.

If you see the Fixed LOD values are impacted by the Context filter.

4.3. Fixed Table-Scoped LOD

There is an additional variation of the predefined set of expressions. Where you do not provide the LOD type and dimensions, you just have the curly braces and the Aggregate function.

{ AGGREGATE}
{ SUM(SALES)}

5. Exclude LOD

The exclude Tableau LOD expressions respects and recognizes all the dimensions in the view, but removes the dimensions that are specified in its calculation, the result is in ATTR (not a scalar value) an aggregated value, affected by the dimension filter. Remember the order of operations.

Let’s assume the same use case of Filter LOD. What if you want your Fixed LOD (the grand total of sales) to be affected by the dimension filters and don’t want to use the context filter everywhere?

We want to exclude the category dimension but to be impacted by the filters.

{ EXCLUDE [Category] : SUM([Sales])}

In the below view, the Exclude LOD value will be impacted by the dimensions filter:

6. Include LOD

As a last of the Tableau LOD expressions, we have the Include LOD expressions.

Similar to the exclude LOD, the include LOD result is in ATTR (aggregated) as an adder, affected by the dimension filters.

The only difference between the exclude and include LOD is that the exclude LOD subtracts or takes away a dimension in its expression, the include LOD includes more dimensions so it puts more detail in so it’s more granular.

Let’s say you want to display the average regional profit for each of these categories but not show regions.

Below is the text table for your reference:

When you create an Include LOD expression to include the region in the dimension list, you will get the below view:

If you compare the cheat sheet, the values would match the total of each region.

6. Conclusion

Tableau LOD expressions provide a powerful tool for including specific dimensions in the analysis, with the ability to be influenced by filters. By utilizing the Include LOD expression, you can incorporate additional dimensions to add more granularity and detail to your data.

For additional information on Tableau, please consult these articles.