From Tableau v9 onwards, a new concept called Level of Detail (LoD) expressions has been introduced. This tutorial will cover the use of LOD expressions in Tableau, different types of LOD expression and how to use them in Tableau. 

What is an LOD Expression?

LOD Expressions provide a way to easily compute aggregations that are not at the level of detail of the visualization. Level of Detail (LOD) expressions are used to run complex queries involving multiple dimensions at the data source level and is a  powerful way to answer questions involving multiple levels of granularity in a single Visualization.

When can we use LOD Expression?

  • When we want to perform a calculation and include a dimension into the calculation which is not a part of the view
  • When we want to perform a calculation on the items in the visualisation but not take into account a certain dimension
  • When we need to show the data at a level different from the dimensions/level present in the view
  • When there is a need to obtain some static calculated value that is not affected by any filters that are applied to the view

Tableau Level of Detail (LOD) Expressions come in three particular flavours:

  • FIXEDThis expression computes values using the specified dimensions without reference to any other dimensions in the view.
  • INCLUDEThis level of detail expressions compute values using the specified dimensions in addition to whatever dimensions are in the view.
  • EXCLUDEThese levels of detail expressions subtract dimensions from the view level of detail.

Fixed Level of Detail

Fixed’ LoD is used when a calculation needs to be done at the level of a particular dimension, irrespective of what is there in the view.

For example, take a look at the following LoD expression:

{ FIXED [Region]:SUM([Sales])}

How to use FIXED LOD

** I have used Sample – Superstore data set to demonstrate LOD expressions

  • Drag Region, State onto Rows.
  • Change the Mark Typeto Text.
  • Drag Sales and @LODFixed onto Text. Leave the aggregation type as SUM.
  • Drag Region to the colour shelf

This produces the following view, where you can see that the LOD expression is calculating the Sales at a Region level, whereas the normal Sales measure is calculated at a Region-State level.

As you can see we have created LOD expressions where we define the level of detail of the Calculation regardless of what is in the current view.

Include Level of Detail

INCLUDE level of detail expressions compute values using the specified dimensions in addition to whatever dimensions are in the view. Example: Calculate the average of sales per state for each category of products in Sample Superstore data set.

Let us start off by creating the following Worksheet:

  • Change the Mark Typeto Bar.
  • Drag Category onto Columns.
  • Drag Sales onto Rows.
    • Right-click on the object, go to Measure (SUM) and select AVG.
    • Ctrl-drag this object onto Label.

You should now see the average Sales by Country.

Imagine if we want to show the Average Sales by State by Category but do not want to show State, we will need to create an INCLUDE Level of Detail Expression:

How to use INCLUDE LOD

{ INCLUDE [State]:SUM([Sales])}

Now let us add Sales (include State) onto Rows and set the type to Average. Ensure that the Mark Shows AVG(Sales (include State)). You should now see.

In accordance with the above statement, even if ‘State’ is not part of a view, the calculation, ‘SUM(Sales)’ here, will be calculated at a State-level.

The image below illustrates the difference between the Average(sales) (somewhere between $350 and $4530 per category) and the average sale per State using Include LOD (between $15458 and $18177 per category)

Exclude Level of Detail

Similar to the ‘Include’ LoD expression, ‘Exclude‘ LoD expression is used when a particular dimension(s) are to be omitted from the level of calculation, out of the dimensions present in the view.

EXCLUDE level of detail expressions are useful for ‘percent of total’ or ‘difference from overall average’ scenarios. They are comparable to such features as Totals and Reference Lines.

EXCLUDE level of detail expression cannot be used in row-level expressions (where there are no dimensions to omit), but can be used to modify either a view level calculation or anything in between (that is, you can use an EXCLUDE calculation to remove dimension from some other level of detail expression).

Let us start off by creating the following worksheet:

  • Change the Mark Typeto Text.
  • Drag Region, State and City onto Rows.
  • Drag Salesonto Text. Leave the aggregation as SUM.

Now imagine that we want to show the Sales in the current view, but exclude a certain Dimension? We will create the following EXCLUDE Level of Detail expression:

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

How to use EXCLUDE LOD

Now double click this newly created Calculated Field to add this to the current view. You should now see the following:

As you can see, we have created a Sum of Sales expression and exclude City from the list of Dimensions.

Order of Execution of Filters and LOD Expressions in Tableau

Below is an image that depicts the order of execution of filters and the LoD expressions that are created in Tableau:

When comparing with SQL, measure filters are equivalent to the HAVING clause and dimension filters are equivalent to the WHERE clause. Hence, the dimension filters will be applied before the measure filters. Also, the text on the right side of the above image shows where the LoD expressions are calculated. The fixed LoD expressions are calculated before the dimension filters and include/exclude LoD expressions are calculated after the dimension filters.

INCLUDE and EXCLUDE level of detail expressions are considered after Dimension filters. So if you want filters to apply to your FIXED level of detail expression but don’t want to use Context Filters, consider rewriting them as INCLUDE or EXCLUDE expressions.

Key Points

  • Not all data sources support LOD expressions
  • LOD expressions do not currently support moving totals, running totals, ordinal computations within a partition (LOOKUP, INDEX, etc.), ranking, or anything like PREVIOUS_VALUE so if we need those kinds of computations then we almost certainly need to use table calculations.
  • If there are large data volumes and relatively few marks then it’s better to  use table calculations because LOD expressions generate subqueries that can have a big performance impact while table calculations are computed locally only across the marks in the view.
  • The fixed LoD expressions are calculated before the dimension filters and include/exclude LoD expressions are calculated after the dimension filters.
  • If the view has a large number of filters in play then it’s good to use EXCLUDE LOD expressions and/or table calculations since they are computed later in the pipeline. Since FIXED LOD expressions require context filters to affect them that can
    1. a) Increase the time it takes to build a view (since all regular filters need to be turned into context filters)
      b) Slow the view down,
      c) Make the view harder to maintain (since every new dimension filter probably needs to be added to the context)
For more information on Level of Detail (LOD) Expressions, check out the following links:
  • Understanding Level of Detail (LOD) Expressions: CLICK HERE
  • Tableau LOD Official Documentation: CLICK HERE
  • Top 15 LOD Expressions by Bethany Lyons: CLICK HERE
  • Tableau online help for LOD expressions: CLICK HERE