LOD Without Tears: FIXED / INCLUDE / EXCLUDE for Everyday Analytics

Tableau’s Level of Detail (LOD) expressions are the fastest way to get stable KPIs, clean percentages, and “compare-to” logic—without fragile table calcs. This guide shows when to use FIXED, INCLUDE, and EXCLUDE, with copy-paste patterns you’ll use every week.When to use which LOD

• FIXED — Calculate at an explicit grain (ignores view level). Use for: stable KPIs (targets, customer counts), denominator for % of total, distinct counts.
• INCLUDE — Calculate at a finer grain, then aggregate back to the view. Use for: row-level logic like “count of distinct days active per customer,” averages of row-derived flags.
• EXCLUDE — Calculate at a coarser grain than the view. Use for: benchmarks, compare-to lines, view-invariant totals (e.g., overall average vs. selected dimension).

Core patterns (copy/paste)

1) Stable distinct counts (FIXED)

“How many unique customers this month—regardless of what dimension is on the view?”

// Distinct customers in current filter context (e.g., date, region)
{ FIXED : COUNTD([Customer ID]) }
        

Add extra scoping if needed:

{ FIXED [Month], [Region] : COUNTD([Customer ID]) }
        

2) % of total that doesn’t break when you slice (FIXED)

Robust denominator that ignores the view’s dimension.

// Numerator respects current slice (e.g., by Product)
[Sliced Sales] = SUM([Sales])

// Denominator fixed to the intended total (e.g., by Region only)
[Total Sales (Region Scope)] = { FIXED [Region] : SUM([Sales]) }

[% of Region Total] = [Sliced Sales] / [Total Sales (Region Scope)]
        

3) Average order value built from row-level orders (INCLUDE)

Roll up after creating order-level metrics.

// Order-level revenue
{ INCLUDE [Order ID] : SUM([Sales]) }  // at order grain

// Average order value in the view context
[AOV] = AVG( { INCLUDE [Order ID] : SUM([Sales]) } )
        

4) Benchmark line (EXCLUDE)

Compare each Product to the overall average in the current filters.

// Avg sales at the overall level (excludes Product)
[Overall Avg Sales] = { EXCLUDE [Product] : AVG([Sales]) }

// Delta vs overall in current context
[Δ vs Overall] = AVG([Sales]) - [Overall Avg Sales]
        

5) Retention/active-days rate (INCLUDE)

Count distinct active days per customer, then average by segment.

[Active Days per Customer] =
{ INCLUDE [Customer ID] : COUNTD([Order Date]) }

[Avg Active Days by Segment] = AVG([Active Days per Customer])
        

6) Top-N share with stable denominator (FIXED + parameter)

“Top 5 products contribute what % of total?”

// Rank by Sales within current filters
[Rank by Sales] = RANK_DENSE(SUM([Sales]))

// Top-N flag
[Is Top N] = [Rank by Sales] <= [p.TopN]

// Stable total for the scope you intend (e.g., category)
[Total Sales (Scope)] = { FIXED [Category] : SUM([Sales]) }

[TopN Share %] =
SUM( IF [Is Top N] THEN [Sales] END ) / [Total Sales (Scope)]
        

Practical tips (so LODs stay fast & correct)

• Scope deliberately. Anything inside { FIXED … } ignores row-level dim changes on the view but still respects filters (unless you set context—see below).
• Use Context Filters for the filters your LOD should respect; non-context filters apply after the LOD is computed.
• Prefer integers for keys in LODs; reduce high-cardinality text where possible.
• Label your intent in calc names: Total Sales (Region Scope) beats Sales LOD.
• Test edge cases: empty slices, single-member partitions, filter changes.

LOD vs Table Calcs (when to choose)

• Choose LOD when you need data-level aggregation independent of the viz shelf (stable totals, denominators, distinct counts).
• Choose Table Calcs when you need layout-aware operations (running totals, window averages, % difference across the visible table).

Many dashboards use LOD for base metrics + table calcs for display (e.g., windowed running sums).

Debug & QA checklist

• Does the LOD respect the right filters? If not, promote those filters to Context.
• Are FIXED dimensions exactly the intent (no accidental grain)?
• Do totals reconcile (e.g., Σ slice = fixed total when appropriate)?
• Performance: any LODs on very high-cardinality fields? Consider extracts or pre-aggregation.
• Tooltips explain scope: “% of Region total (ignores Product)”.

Common pitfalls

• FIXED without understanding filter order → totals “mysteriously” ignore a slicer.
• Using EXCLUDE when you meant a view-invariant total (often FIXED is better).
• Nesting heavy LODs on raw, row-exploded data—extract or summarize first.
• Ambiguous denominators for % of total (document scope in the calc name).

How Insights Chronicle can help

We standardise your Tableau KPIs with clear LOD patterns—stable denominators, clean %-of-total, robust benchmarks—and document scope so every analyst builds consistent, fast visuals.

Contact: alex@insightschronicle.com Website: insightschronicle.com