Level of Detail (LOD) Expressions

Level of Detail expressions, commonly called LOD expressions, are one of the most transformative features ever added to Tableau. Introduced in Tableau 9.0, they solve a class of problems that were previously impossible or required complex workarounds: computing an aggregate at a different granularity than what the view is showing.

Understanding LOD expressions is a turning point in becoming a proficient Tableau developer. This chapter explains the problem they solve, the syntax and semantics of all three types, how they interact with Tableau's filter pipeline, and detailed worked examples covering the most common real-world use cases.

---

The Problem LOD Expressions Solve

Every Tableau view has a level of detail — the granularity at which data is aggregated. That level is determined by the dimensions placed on Rows, Columns, and the Mark's Detail shelf. If you put [Category] and [Year] in the view, Tableau computes aggregates (SUM, AVG, COUNT, etc.) at the Category × Year level.

The problem arises when you need a calculation at a different level of granularity than the view.

A Concrete Example

Suppose you want to show each customer's total lifetime purchases alongside their individual order values. The view is at the order level (one row per order), but the customer total is a customer-level aggregate. Without LOD expressions, you cannot do this directly — any aggregate in the view would operate at the current view level (the order level), giving you each order's value, not each customer's total.

You might try using a table calculation (like RUNNING_SUM) to accumulate per-customer, but table calculations depend on how data is sorted and addressed in the view, and they break when you filter or re-sort.

The correct solution is an LOD expression:

{ FIXED [Customer ID] : SUM([Sales]) }

This tells Tableau: "Compute SUM(Sales) grouped by Customer ID, regardless of what level the view is at." The result is a single number per customer that remains correct whether the view is showing individual orders, monthly totals, or anything else.

---

What Is a Level of Detail Expression?

An LOD expression is a special Tableau calculation enclosed in curly braces {} that specifies:

1. A set of dimensions to group by (the "level")
2. An aggregation to compute at that level

The result is joined back to the view's data based on the specified dimensions. Every mark in the view gets the LOD value corresponding to its dimension members.

General Syntax

{ LOD_TYPE [Dimension1], [Dimension2], ... : AGGREGATE([Measure]) }

Where LOD_TYPE is one of: FIXED, INCLUDE, or EXCLUDE.

---

The Three LOD Types

1. FIXED

FIXED computes an aggregate at precisely the dimensions you specify, completely ignoring the dimensions in the current view (with one important exception described below).

Syntax:

{ FIXED [Dimension1], [Dimension2] : AGG([Measure]) }

Behavior: The specified dimensions determine the level of granularity. The view's dimensions are irrelevant — the result is always the same no matter what is on Rows, Columns, or Filters (with exceptions for high-priority filters described later).

Example: Customer's first order date:

{ FIXED [Customer ID] : MIN([Order Date]) }

No matter how the view is sliced (by region, by year, by product), every mark associated with Customer "John Smith" gets the same value: John Smith's earliest order date.

When FIXED Respects Filters:

FIXED ignores dimension filters and measure filters (Steps 4–6 in the filter order of operations), but it does respect:

• Extract Filters — the data simply does not exist in the extract
• Data Source Filters — rows excluded at this level are never seen by FIXED
• Context Filters — FIXED re-evaluates after the context filter reduces the dataset

This is a critical point: if you want a filter to affect a FIXED LOD expression, you must promote that filter to a Context Filter.

---

2. INCLUDE

INCLUDE adds dimensions to the view's current level of detail for the purpose of this calculation. The result is then aggregated back up to the view level.

Syntax:

{ INCLUDE [Dimension] : AGG([Measure]) }

Behavior: Tableau computes the aggregation at a finer grain than the view (the view's dimensions PLUS the INCLUDE dimension), then aggregates those results up to the view level.

Example: Average sales per customer, in a view showing data by Region.

The view is at the Region level. But you want the average across individual customers within each region.

{ INCLUDE [Customer ID] : SUM([Sales]) }

Step 1: Tableau computes SUM(Sales) per Region × Customer ID (finer than the Region view). Step 2: In the view, Tableau averages those per-customer totals within each Region.

The final result: the average customer total per region — a metric that requires going to a finer grain and aggregating back up.

Key Rule: INCLUDE LOD expressions produce values at a finer grain than the view, so they must be further aggregated when placed in the view (e.g., AVG({ INCLUDE [Customer ID] : SUM([Sales]) })).

---

3. EXCLUDE

EXCLUDE removes dimensions from the view's current level of detail for the purpose of this calculation. It computes at a coarser grain than the view.

Syntax:

{ EXCLUDE [Dimension] : AGG([Measure]) }

Behavior: Tableau computes the aggregation at a coarser grain than the view (the view's dimensions MINUS the EXCLUDE dimension).

Example: Percent of total, where the view shows Sub-Category within Category.

The view has both [Category] and [Sub-Category]. If you write SUM([Sales]), you get the Sub-Category × Category total. But to compute percent of total, you need the Category-level total (without Sub-Category). EXCLUDE removes Sub-Category from the computation:

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

This computes SUM(Sales) per Category only, giving you the Category total for every Sub-Category row in that Category. You can then divide:

SUM([Sales]) / { EXCLUDE [Sub-Category] : SUM([Sales]) }

Result: each Sub-Category's share of its parent Category's total sales.

---

How LOD Expressions Interact with the Filter Order

Understanding where LOD expressions fall in Tableau's filter pipeline is essential for getting correct results.

Filter Type	Affects FIXED?	Affects INCLUDE?	Affects EXCLUDE?
Extract Filter	Yes	Yes	Yes
Data Source Filter	Yes	Yes	Yes
Context Filter	Yes	Yes	Yes
Top N / Condition Filter	No	Yes	Yes
Dimension Filter	No	Yes	Yes
Measure Filter	No	Yes	Yes
Table Calculation Filter	No	No	No

The key insight: FIXED LOD expressions are computed before dimension filters apply. This means if you filter to show only the "West" region, a FIXED LOD expression that computes at the Customer level will still include customers from all regions in its calculation unless you promote the Region filter to a Context Filter.

This is intentional behavior. FIXED is designed to provide stable, filter-immune aggregations. Use it when you want a global metric that does not change with user filtering. Use INCLUDE or EXCLUDE when you want the LOD expression to respect the current view filters.

---

Common LOD Patterns with Full Worked Examples

Pattern 1: Customer First Purchase Date

Business question: When did each customer first buy from us?

LOD expression:

{ FIXED [Customer ID] : MIN([Order Date]) }

How to use it:

1. Create a calculated field named "First Purchase Date" with the above expression.
2. Place [Customer Name] on Rows, [First Purchase Date] on Columns.
3. Every customer row shows their earliest order date, regardless of how you filter the view by product or region.

Extension — New vs. Returning Customer:

IF [Order Date] = { FIXED [Customer ID] : MIN([Order Date]) }
THEN "New Customer"
ELSE "Returning Customer"
END

This row-level calculated field compares each order's date to the customer's first-ever order date, tagging first orders as "New Customer."

---

Pattern 2: Average Orders per Customer

Business question: How many distinct orders does the average customer place?

LOD expression:

{ FIXED [Customer ID] : COUNTD([Order ID]) }

How to use it in a view:

AVG({ FIXED [Customer ID] : COUNTD([Order ID]) })

The FIXED expression computes, for each Customer ID, the distinct count of their orders. Wrapping in AVG in the view averages those counts across all customers visible in the view.

Why not just AVG(COUNTD([Order ID]))? In a view without Customer on a shelf, AVG(COUNTD([Order ID])) would compute the count of distinct orders at the view's level of detail (e.g., per Region), not per customer. The LOD expression explicitly sets the granularity to Customer ID.

---

Pattern 3: Cohort Analysis — Group by First Purchase Month

Business question: How do customers acquired in different months behave over time?

Step 1: Create the cohort assignment:

{ FIXED [Customer ID] : MIN(DATETRUNC('month', [Order Date])) }

Name this "Acquisition Month." It gives every customer a single date: the month they first appeared.

Step 2: In your analysis view, place [Acquisition Month] on Rows (or Color), and a time dimension (e.g., months since first purchase) on Columns. This groups customers by their entry cohort.

Step 3: To compute months since acquisition for each order:

DATEDIFF('month',
  { FIXED [Customer ID] : MIN([Order Date]) },
  [Order Date]
)

Step 4: Build a heat map or line chart with [Acquisition Month] on Rows, [Months Since Acquisition] on Columns, and AVG(Sales) or Customer Retention Rate as the metric.

This is the classic cohort retention chart — one of the most valuable analyses for subscription businesses — and it is cleanly powered by a FIXED LOD expression.

---

Pattern 4: Percent of Total by Category (EXCLUDE)

Business question: What percent of each Category's sales does each Sub-Category represent?

View setup: [Category] and [Sub-Category] on Rows, SUM([Sales]) as a measure.

LOD expression:

SUM([Sales]) / { EXCLUDE [Sub-Category] : SUM([Sales]) }

The { EXCLUDE [Sub-Category] : SUM([Sales]) } part computes Sales at the Category level (the view has Category + Sub-Category; removing Sub-Category leaves just Category). The division then gives each Sub-Category's share.

Format this calculated field as a percentage. The result: a table where each Sub-Category row shows its contribution to the parent Category's total, summing to 100% within each Category group.

---

Pattern 5: Regional vs. National Comparison (FIXED at Higher Level)

Business question: How does each Region's average order value compare to the national average?

National average (FIXED to nothing — computes across all rows):

{ FIXED : AVG([Sales]) }

A FIXED with no dimensions specified computes a single value for the entire dataset (a "grand total" LOD).

Regional vs. National:

AVG([Sales]) - { FIXED : AVG([Sales]) }

This shows how much above or below the national average each region performs.

Extending to a different level:

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

In a view at the State level, this gives every state in the South region the same value: the total sales for the entire South region. Useful for creating reference lines or comparison columns.

---

Pattern 6: Sales Per Day (Normalize by Number of Days)

Business question: What is the average daily sales rate per month?

Step 1: Count the number of distinct order days in each month:

{ INCLUDE [Order Date] : COUNTD([Order Date]) }

Wait — this counts distinct dates at a finer grain (Order Date) than the view (Month). The INCLUDE adds [Order Date] to the computation, giving us distinct date counts per Month × Order Date. We then aggregate back to the month level:

COUNTD({ INCLUDE [Order Date] : COUNTD([Order Date]) })

Actually, a cleaner approach is:

{ FIXED [MONTH([Order Date])], [YEAR([Order Date])] : COUNTD([Order Date]) }

Step 2: Daily rate:

SUM([Sales]) / { FIXED [MONTH([Order Date])], [YEAR([Order Date])] : COUNTD([Order Date]) }

This normalizes monthly sales by the number of days in which orders were actually placed, giving a true daily rate that is comparable across months with different numbers of active business days.

---

FIXED vs. INCLUDE vs. EXCLUDE: Decision Guide

Question	Answer	Use
Do you need the calculation at a completely fixed granularity, immune to view filters?	Yes	FIXED
Do you need to compute at a finer grain than the view and then aggregate back up?	Yes	INCLUDE
Do you need to compute at a coarser grain than the view (remove a dimension)?	Yes	EXCLUDE
Should the LOD respect dimension filters?	Yes	INCLUDE or EXCLUDE
Should the LOD ignore dimension filters?	Yes	FIXED (or FIXED + Context Filter for selective respect)
Is the computation about "within each member of X, what is the Y"?	Yes	FIXED
Is the computation about "per sub-entity, averaged at the current level"?	Yes	INCLUDE
Is the computation about "total at the parent level for comparison"?	Yes	EXCLUDE

Quick Syntax Reference

{ FIXED [Dim1], [Dim2] : SUM([Measure]) }
{ INCLUDE [Dim] : SUM([Measure]) }
{ EXCLUDE [Dim] : SUM([Measure]) }
{ FIXED : SUM([Measure]) }   -- Grand total (no dimensions)

---

LOD Expressions with Parameters

Parameters make LOD expressions dynamic. A common pattern: let the user select which dimension to use as the LOD grouping.

Scenario: The user can choose between "Customer" and "Region" as the grouping level for a lifetime value calculation.

Step 1: Create a String parameter named "LOD Level" with values "Customer ID" and "Region."

Step 2: Create a calculated field that uses a CASE statement:

CASE [LOD Level]
  WHEN 'Customer ID' THEN { FIXED [Customer ID] : SUM([Sales]) }
  WHEN 'Region' THEN { FIXED [Region] : SUM([Sales]) }
END

Show the parameter control. Users can now toggle the LOD level and see how metrics change when aggregated by customer vs. by region.

Limitation: You cannot dynamically pass a parameter as the dimension argument inside an LOD expression itself (Tableau does not support { FIXED [Parameter] : AGG } because the dimension name must be a field reference). The CASE workaround above is the standard solution.

---

Performance Considerations for LOD Expressions

LOD expressions have significant performance characteristics that differ from table calculations:

LODs run on the database, not in Tableau's engine. Unlike table calculations, LODs are translated into SQL subqueries or joins that run on the database server. For a FIXED LOD, Tableau generates a query that groups by the FIXED dimensions, then joins the result back to the main query. For live connections to large databases, this means LOD performance depends on database capacity, indexes, and query optimizer behavior.

FIXED LODs can be expensive on large tables. Because they ignore view filters (unless promoted to context), a FIXED LOD may scan the entire table even when you are viewing a small subset of data. On a table with 100 million rows, { FIXED [Customer ID] : MIN([Order Date]) } requires a full pass of the table to find each customer's minimum date.

Optimization strategies:

Strategy	How
Use extracts	LOD queries run much faster on Hyper extracts than on live connections to row-store databases
Add database indexes	Index the dimensions used in FIXED LODs (e.g., [Customer ID])
Promote critical filters to context	Reduces the data volume that FIXED LODs must scan
Cache repeated LODs	Tableau caches LOD results within a session; avoid building the same LOD multiple times in different calculated fields — reuse one
Avoid LODs on string functions	{ FIXED LOWER([Customer Name]) : ... } prevents index use; clean data at source
Use Data Source Filters aggressively	They cut data before LOD queries run

---

LOD vs. Table Calculations: Which to Use When

Both LODs and table calculations can compute metrics across groups of data, but they work very differently:

Dimension	LOD Expressions	Table Calculations
Where it runs	Database (SQL subquery)	Tableau engine (in memory)
Interacts with filters	Selectively (see filter order)	Only after all other filters
Requires specific view structure	No — works at any view level	Yes — depends on what is in the view
Can be used in row-level calcs	Yes (FIXED result is a row-level value)	No
Can be used in filters	Yes (drag to Filters shelf directly)	Only as table calculation filter (slowest)
Restarts with partitions	No concept of addressing	Yes — controlled by addressing/partitioning
Best for	Cross-row aggregations at fixed granularity	Sequential operations (rank, running totals, comparisons to adjacent rows)

Choose LOD when:

• You need a stable metric that does not change as the user changes filters
• You need to compute at a different granularity than the view and use the result in further calculations
• You need the result available as a dimension or filter
• Performance on the database is acceptable (good indexes, or using an extract)

Choose Table Calculations when:

• You need to reference adjacent rows (previous, next, first, last)
• You need cumulative calculations (running sum, running avg)
• You need rankings within a view partition
• Your analysis is inherently sequential (time series, ranked lists)

---

Practice Exercises

Exercise 1: Customer Lifetime Value Segmentation

Dataset: Sample - Superstore

Task:

1. Create a FIXED LOD expression for each customer's total lifetime sales:

   { FIXED [Customer Name] : SUM([Sales]) }
   

2. Create a calculated field "LTV Segment":

   IF { FIXED [Customer Name] : SUM([Sales]) } >= 5000 THEN "High Value"
   ELSEIF { FIXED [Customer Name] : SUM([Sales]) } >= 2000 THEN "Mid Value"
   ELSE "Low Value"
   END
   

3. Build a bar chart showing COUNT of customers in each LTV Segment.
4. Add a Region filter. Observe: the LTV Segment does NOT change when you filter by Region (because FIXED ignores dimension filters).
5. Promote the Region filter to a Context Filter. Observe: now LTV segments are computed only within the selected region.

Expected Insight: Context filters are the only way to make FIXED LODs respect dimension-level restrictions.

---

Exercise 2: Average Order Value per Customer in Each Region

Dataset: Sample - Superstore

Task:

1. Build a view with [Region] on Rows.
2. Create an INCLUDE LOD expression:

   { INCLUDE [Customer Name] : SUM([Sales]) }
   

3. Add this to the view as AVG({ INCLUDE [Customer Name] : SUM([Sales]) }).
4. Also add AVG([Sales]) to the view as a comparison.
5. Notice: AVG(Sales) is the average per order. AVG({ INCLUDE [Customer Name] : SUM([Sales]) }) is the average per-customer total within each region.
6. Add a text annotation explaining the difference.

Expected Insight: INCLUDE lets you compute per-customer totals even in a view that does not have Customer as a dimension.

---

Exercise 3: Sub-Category Percent of Category

Dataset: Sample - Superstore

Task:

1. Build a text table with [Category] and [Sub-Category] on Rows, SUM([Sales]) on Text.
2. Create a calculated field "% of Category":

   SUM([Sales]) / { EXCLUDE [Sub-Category] : SUM([Sales]) }
   

3. Format as a percentage with 1 decimal place.
4. Add this field as a second text column.
5. Add a Category filter. Verify: selecting "Technology" shows only Technology's sub-categories, and their percentages still sum to 100% (not 100% of the full dataset).

Expected Insight: EXCLUDE computes at the parent level, enabling natural percent-of-group calculations that respond correctly to dimension filters.

---

Exercise 4: Cohort Retention Analysis

Dataset: Sample - Superstore

Task:

1. Create "First Purchase Month":

   { FIXED [Customer Name] : MIN(DATETRUNC('month', [Order Date])) }
   

2. Create "Months Since First Purchase":

   DATEDIFF('month',
     { FIXED [Customer Name] : MIN([Order Date]) },
     DATETRUNC('month', [Order Date])
   )
   

3. Build a crosstab: [First Purchase Month] on Rows, [Months Since First Purchase] on Columns.
4. Metric: COUNTD([Customer Name]) — distinct customers active in each cohort × month combination.
5. Add color based on the metric (sequential color, white to dark blue).
6. The result is a cohort retention heat map: the diagonal represents each cohort's first month; values to the right show how many customers from each cohort returned in subsequent months.

Solution Explanation:

• Row "January 2021" shows all customers who first purchased in January 2021.
• Column 0 = how many of those customers bought in January 2021.
• Column 1 = how many returned to buy in February 2021.
• Column 6 = how many returned 6 months later.
• Reading down each column shows how retention compares across cohorts at the same lifecycle stage.

Expected Insight: LOD expressions enable cohort analysis by creating stable per-entity attributes (first purchase date) that can be used as group keys across any view granularity.

---

Summary

In this chapter you learned:

• The problem LOD expressions solve — computing aggregations at a different granularity than the current view, which neither row-level calculations nor aggregate calculations can do directly.

• FIXED — computes at explicitly specified dimensions, ignoring view dimensions and most filters. Respects Extract, Data Source, and Context Filters only. The most commonly used LOD type. Syntax: { FIXED [Dim] : AGG([Measure]) }.

• INCLUDE — adds dimensions to the view's level for this computation, then aggregates back up. Produces finer-grain results that must be further aggregated in the view. Respects all filters. Syntax: { INCLUDE [Dim] : AGG([Measure]) }.

• EXCLUDE — removes dimensions from the view's level for this computation, producing coarser-grain results. Essential for "percent of total at parent level" patterns. Respects all filters. Syntax: { EXCLUDE [Dim] : AGG([Measure]) }.

• Filter order interaction — FIXED is computed before dimension/measure filters. To make a FIXED LOD respect a dimension filter, promote that filter to a Context Filter.

• Common patterns — customer first purchase date (FIXED + MIN), average orders per customer (FIXED + COUNTD), cohort analysis (FIXED first purchase month), percent of category (EXCLUDE), regional vs. national comparison (FIXED with no dimensions), and sales per day (FIXED + COUNTD of dates).

• LOD with Parameters — use CASE statements to switch between different FIXED LOD computations based on a parameter value, enabling dynamic LOD control.

• Performance — LOD expressions run on the database as subqueries. Use extracts for speed, add database indexes on LOD dimensions, promote context filters to restrict data volume, and avoid LODs on computed string functions.

• LOD vs. Table Calculations — LODs run on the database and work at any view level; table calculations run in Tableau's engine and depend on the view structure. LODs for stable metrics and cross-level aggregation; table calculations for sequential, positional, and cumulative operations.

LOD expressions are the bridge between two analytical worlds: the database-level aggregation world and the visualization-level display world. Once you are comfortable writing FIXED, INCLUDE, and EXCLUDE expressions, you will find that an entire category of previously complex Tableau challenges becomes straightforward.