Thanks a LOD: A Guide to Fixed, Include, and Exclude LODs

In Tableau, everything is governed by the Viz "Level of Detail." If you drag Region to a row, your data aggregates to the Region level. If you add State, it aggregates to the State level. This is usually helpful, but real-world business questions often ignore these rules.

Analysts are frequently asked questions that fight against the view structure: "How do I filter to 2024 but compare it against the all-time average?" "How can I see average spend per customer, even though I’m looking at a map of Countries?"

This is where Level of Detail (LOD) expressions come in. They allow you to calculate values at a specific level of granularity, independent of what is currently on your screen.

The Syntax: Core Vocabulary

LOD expressions might look complex, but they follow a strict, logical sentence structure wrapped in curly braces:

{ [LOD TYPE] [DIMENSION] : [AGGREGATE_EXPRESSION] }

This tells Tableau: "Group the data by [DIMENSION], calculate the [AGGREGATE], and apply the rule of [TYPE]."

The Three Types

There are only three keywords you need to know.

1. FIXED (The Independent) Calculates values using only the specified dimensions, ignoring the view entirely.

• Best for: Cohort analysis, "First ever" dates, or benchmarks that shouldn't change when you filter.

2. INCLUDE (The Drill-Down) Calculates values using the specified dimensions plus whatever is in the view.

• Best for: Calculating averages of a total (e.g., Average total transaction size per customer).

3. EXCLUDE (The Zoom-Out) Calculates values ignoring specific dimensions that are currently in the view.

• Best for: Percent of total calculations where you need a "Grand Total" denominator while breaking down data in the view.

Real-World Examples

Below are three scenarios commonly faced by analysts, demonstrating the Objective, the LOD Pattern, and the Result.

Example 1: The "First Purchase" Date (FIXED)

Objective: You want to calculate the days since a customer’s first purchase. To do this, you need their first order date to remain static on every row, regardless of other filters.

The Problem: Standard MIN([Order Date]) changes if you filter out previous years.

LOD Pattern:

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

Result: Tableau creates a fixed date stamp for every customer. Even if you look at their 2024 orders, this field will still remind you that their journey started in 2019.

Example 2: Average Transaction Value (INCLUDE)

Objective: You have a bar chart showing Region. You want to see the average amount spent per Order.

The Problem: If you simply use AVG([Sales]), Tableau averages the line items in the data, not the total order baskets. You need to sum the order first, then average it.

LOD Pattern:

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

Result:

Tableau goes "deeper" than the Region view to sum up every Order ID first. When you drag this field to your chart and aggregate by Average, you get the true Average Order Value.

Example 3: Percent of Total Sales (EXCLUDE)

Objective: You have a breakdown of Sales by Segment (Consumer, Corporate, Home Office). You want to calculate what percentage each Segment contributes to the whole.

The Problem: To get a percentage, you need the formula: Segment Sales / Total Sales. The view breaks the sales into segments, so we need a way to calculate the Total without that break.

LOD Pattern:

SUM([Sales]) / SUM( { EXCLUDE [Segment] : SUM([Sales]) } )

Result: The denominator ignores the Segment breakdown and returns the total sum, allowing for a precise ratio calculation.

Top Tips & Common Pitfalls

1. The Order of Operations

The most common reason a FIXED LOD fails is filtering. By default, FIXED expressions calculate before standard dimension filters.

• The Issue: If you filter "Year" to 2023, a FIXED calculation for Total Sales will still calculate for all years because it ignores the filter.
• The Fix: Right-click your filter and select "Add to Context." Context filters execute before FIXED LODs.

2. Aggregate vs. Row Level

Remember the output state:

• FIXED returns a value for every row (Non-Aggregate).
• INCLUDE/EXCLUDE return aggregated data.
• Tip: You generally cannot mix these directly in logical statements (IF/THEN) without wrapping the fixed LOD in an aggregation like SUM() or ATTR().

Conclusion

LOD Expressions are a high-leverage skill for data professionals. While they require an initial mindset shift to understand "granularity," the return on investment is massive. They transform Tableau from a simple sketching tool into a robust analytical engine, allowing you to answer questions that standard drag-and-drop actions simply cannot.

-- Tyler