Chapter 4: Level of Detail (LOD) Expressions – Tableau Calculated Fields Tutorial

The Game Changer Using Sample – Superstore dataset

LOD expressions let you compute values at a different level of granularity than what’s in your current view. This solves one of the biggest limitations in regular aggregations: “I want the total sales per customer, but shown per order” or “average profit per customer, ignoring filters.”

LOD syntax: { [FIXED | INCLUDE | EXCLUDE] <dimension(s)> : <aggregation(expression)> }

Here are the three main types with visual explanations:

Here are some classic diagrams showing how FIXED, INCLUDE, and EXCLUDE work:

Quick Comparison Table

Type	Scope	Ignores view filters?	Best for	Example in Superstore
FIXED	Fixed dimensions (ignores filters)	Yes	Cohort analysis, % of total	% of total sales per category
INCLUDE	Adds dimensions to view level	No	Customer-level calcs in order view	Avg order value per customer
EXCLUDE	Removes dimensions from view level	No	Grand totals, window calcs	Profit per customer ignoring sub-category

10 Practical LOD Examples on Sample – Superstore

1. FIXED: % of Total Sales by Category (ignores all filters)

Name: Category % of Total Sales Formula:

text

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

→ This gives each category’s contribution to overall company sales, even if you filter to one region. Drag to Label or Color on a Category bar chart.

2. FIXED: Sales per Customer (cohort-style, fixed at customer level)

Name: Customer Lifetime Sales Formula:

text

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

→ Shows total sales for each customer, repeated on every row/order for that customer. Useful for coloring orders by customer value.

Here’s a visual of customer-level aggregation in action:

Understanding Level of Detail (LOD) Expressions with Tableau

3. INCLUDE: Average Order Value per Customer

Name: Avg Order Value per Customer Formula:

text

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

→ Computes AOV at customer level, then aggregates in view. Great when view is by Region or Year.

4. EXCLUDE: Profit per Customer (ignoring Sub-Category)

Name: Profit per Customer (ignore Sub-Cat) Formula:

text

{ EXCLUDE [Sub-Category] : SUM([Profit]) / COUNTD([Customer ID]) }

→ Shows average profit per customer, even if Sub-Category is in the view.

5. FIXED for Cohort: First Purchase Year per Customer

Name: Customer Cohort Year Formula:

text

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

→ Tags every order with the year the customer first bought. Use on Rows with COUNTD([Customer ID]) to see retention cohorts.

Here’s an example of cohort-style LOD in Superstore:

15 Examples of Tableau LOD – Cohort Analysis – The Data School …

6. % of Sales within Region (FIXED at Region)

Name: % Sales within Region Formula:

text

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

→ Each state/city shows % of its region’s total sales.

7. Customer Count with > $10K Lifetime Sales (FIXED + filter)

Name: High Value Customer Flag Formula:

text

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

→ Boolean TRUE/FALSE. Use as filter or color.

8. INCLUDE: Daily Profit KPI (per customer per day)

Name: Daily Profit per Customer Formula:

{ INCLUDE [Customer ID], DATETRUNC('day', [Order Date]) : SUM([Profit]) }

→ Useful for time-series analysis at customer-day level.

Example visualization:

15 Examples of Tableau LOD – Daily Profit KPI – The Data School …

9. EXCLUDE for Grand Total Average

Name: Overall Avg Discount (ignores all dimensions) Formula:

AVG({ EXCLUDE [Region],[Category],[Sub-Category] : [Discount] })

→ Shows company-wide average discount as a constant line.

10. Combined FIXED + INCLUDE: Rank Customers by Lifetime Sales within Region

Name: Customer Rank in Region Formula:

text

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

→ Compute Using → Customer ID (advanced ranking).

Chapter 4 Mini Dashboard Challenge

Build a “Customer 360” dashboard:

1. Bar: Sales by Category colored by % of Total (Ex1)
2. Scatter: Customer Lifetime Sales (Ex2) vs Number of Orders
3. Heatmap: Cohort Year (Ex5) × Region with COUNT customers
4. Line: Monthly Sales + Overall Avg Profit per Customer (Ex4)
5. Filter: High Value Customers only (Ex7)

LOD takes time to master, but once you get it, you unlock almost any analysis.

Common Pitfalls

• Don’t forget the colon :
• FIXED ignores filters (good & bad)
• Performance: Avoid too many nested LODs