Level of Detail (LOD) expressions — Tableau Module 5

Level of Detail expressions are Tableau's most powerful feature and its most-tested in interviews. They solve a specific problem: how to compute an aggregate at one grain (e.g., total revenue per customer) while visualising at another grain (e.g., individual orders). Before LODs, this required either data prep, table calculations, or contorted workarounds. After LODs, it's one line.

The three LOD keywords

FIXED [dim1], [dim2]: compute at exactly this level of detail, ignoring the view's level. The most common.
INCLUDE [dim]: compute at the view's level PLUS this dimension. Useful when the view doesn't show the dimension but you want to compute as if it did.
EXCLUDE [dim]: compute at the view's level MINUS this dimension. Useful for 'percentage of category total' when category is on the view.

The classic problem: percent of category total

You have a view showing sales by sub-category, broken down by product. You want each product's percentage of its parent category's total. With a simple aggregation, the denominator is wrong — it uses the product's level, not the category's level.

text

// The LOD solution
% of Category = SUM([Sales]) / SUM({ FIXED [Category] : SUM([Sales]) })

// Read literally: revenue of this row, divided by
// revenue summed up by Category (ignoring other dimensions in the view).

FIXED LOD pins the denominator to the Category level, regardless of what else is in the view. Exactly what was needed.

The mental model

Inside the curly braces is a 'mini-query' that ignores the view's other dimensions. Outside the braces, you use it as a regular aggregated value. The pattern — { FIXED dim : aggregate } — is the most-written calculation in advanced Tableau, by a wide margin.

When each LOD type wins

Customer's first order date: { FIXED [Customer ID] : MIN([Order Date]) } — pinned per-customer regardless of view.
Average order value per customer, displayed per region: { INCLUDE [Customer ID] : AVG([Order Total]) } — view is by Region but the average is per-customer first, then averaged.
Sales as % of regional total, regardless of segment shown: { EXCLUDE [Segment] : SUM([Sales]) } as denominator — pulls Segment out of the calculation's grain.

Exercise

Build a view showing each customer's first order date and their lifetime value. Use FIXED to pin both calculations to the Customer ID level. Now add a filter for 'first order date in 2024' — does it filter customers acquired in 2024, or orders placed in 2024 by any customer? Why?