Tableau LOD Expression Returns Wrong Total Fix

Your LOD total looks authoritative and is wrong. The view shows regional sales summing to $2.1M, but your { FIXED : SUM([Sales]) } grand total insists on $2.8M. No error, no warning — just a confident number that disagrees with visible rows. This is Tableau's most expensive calculation bug because it never announces itself.

FIXED, INCLUDE, and EXCLUDE each declare a grouping level independent of the view, and that independence is both the power and the trap. The expression computes at its declared grain, the view renders at its own grain, and dimension filters apply at a third point in the pipeline. Any mismatch among the three produces plausible wrong numbers.

Analysts usually discover the damage downstream: a bonus paid on inflated totals, a forecast anchored to a denominator that ignored the region filter, a board slide where percentages sum to 140%. By then the LOD has been copied into five workbooks and nobody remembers which filter it was supposed to respect.

This article teaches scope the way the engine sees it. You'll learn exactly what each keyword computes, how filters interact with each, and the table-calc replication technique that proves any LOD correct in minutes. Wrong totals become diagnosable — and preventable.

Scope in One Paragraph: What FIXED, INCLUDE, and EXCLUDE Compute

FIXED declares absolute grain: { FIXED [Region] : SUM([Sales]) } totals per region no matter what the view holds. INCLUDE declares view-grain-plus: { INCLUDE [Customer] : SUM([Sales]) } computes at the view's dimensions plus Customer, going finer. EXCLUDE declares view-grain-minus: { EXCLUDE [Region] : SUM([Sales]) } computes at the view's dimensions without Region, going coarser. Three keywords, three relationships to the view.

Bare FIXED with no dimensions — { FIXED : SUM([Sales]) } — is the grand total at the whole-data level, the most copied and most dangerous LOD in circulation. It ignores every dimension filter by default, which is correct for a company-wide denominator and catastrophic for a filtered regional view. Know which population you mean before you type the colon.

Level of detail flows from these definitions, not from the view. A FIXED [Customer] total beside Order-level rows repeats the customer figure on each of their orders — correct behavior that looks like duplication to beginners. Summing those repeated cells double-counts; averaging or taking MIN of them is the right rollup. The grain you declared dictates the rollup you may use.

Order of operations completes the picture. FIXED computes after context filters and data-source filters but before dimension filters, sets, and view layout. INCLUDE and EXCLUDE compute after dimension filters, so they respect them. That single ordering fact explains nearly every 'my LOD ignores my filter' ticket.

Memorize the trio as absolute, finer, coarser — and FIXED as filter-blind by default. Every debugging step in this article is an application of those five words.

// FIXED: absolute grain, ignores dimension filters by default
{ FIXED [Region] : SUM([Sales]) }

// INCLUDE: view grain PLUS Customer (finer than the view)
{ INCLUDE [Customer ID] : SUM([Sales]) }

// EXCLUDE: view grain MINUS Region (coarser than the view)
{ EXCLUDE [Region] : SUM([Sales]) }

// Grand total: whole-data level, most dangerous LOD in circulation
{ FIXED : SUM([Sales]) }

FIXED vs Dimension Filters: The Order of Operations Trap

Tableau filters apply in a strict pipeline: data-source filters, context filters, then FIXED LODs, then dimension filters, sets, and table calcs. FIXED sits mid-pipeline, blind to everything below it. A Region dimension filter shrinks rows but leaves every FIXED total counting the unfiltered population — numerators and denominators silently describe different worlds.

Context filters are the sanctioned override. Right-click > Add to Context promotes a filter above FIXED computation; the pill turns gray to show its status. Promote exactly the filters the business question requires: West-only attainment needs Region in context, while a company-wide benchmark denominator must keep Region as a dimension filter so FIXED keeps ignoring it.

Sets and combined fields sit below FIXED too, which surprises analysts migrating set-driven logic into LODs. A top-10-customer set won't constrain a FIXED total. Restructure with INCLUDE (which respects sets) or pre-filter via context, and verify the total moves when the set changes.

Parameters bypass the whole issue elegantly. Because LOD expressions can reference parameters directly, { FIXED : SUM(IF [Region Param] = [Region] THEN [Sales] END) } builds the filter into the computation itself. Parameter-driven LODs behave identically in every view, immune to pill placement — at the cost of single-select simplicity.

Audit every FIXED expression against its view's filter shelf before publishing. For each filter ask: should the total respect this? Context for yes, dimension for no, comment for the reasoning. Undecided filters are future incidents.

// Filter-aware FIXED: Region must be a CONTEXT filter (gray pill)
{ FIXED [Customer ID] : SUM([Sales]) }
// Toggle test: change Region filter -> total MUST move.
// If frozen, the filter is a dimension filter: promote it.

// Parameter-driven alternative: immune to pill placement
{ FIXED : SUM(IF [Region] = [Region Param] THEN [Sales] END) }

INCLUDE and EXCLUDE: Relative Grain Done Right

INCLUDE and EXCLUDE derive grain from the view, which makes them flexible and layout-sensitive. { INCLUDE [Segment] : SUM([Sales]) } in a Region view computes per Region × Segment; move it to a Category view and it computes per Category × Segment. Same expression, different numbers — correct in both, confusing when copied between sheets.

INCLUDE shines for per-group averages: AVG({ INCLUDE [Order ID] : SUM([Sales]) }) gives average order value at whatever view level you slice, because the inner expression pins order grain while the outer AVG follows the view. That two-level dance is the canonical INCLUDE pattern; learn it once and reuse it everywhere.

EXCLUDE shines for share-of-parent math: SUM([Sales]) / SUM({ EXCLUDE [Sub-Category] : SUM([Sales]) }) shows each sub-category's share of its category total, adapting automatically as views drill. The denominator always sits one level above the view — until someone removes the parent dimension, collapsing denominator to grand total silently.

That layout sensitivity is the hazard. An EXCLUDE built in a Region × Segment view that gets reused in a Segment-only view now excludes a dimension that isn't there, returning identical values on every row. The calc didn't break; its frame of reference moved. FIXED with explicit dimensions is sturdier for expressions that travel between sheets.

Choose relative keywords for view-bound analysis that lives on one sheet, absolute FIXED for shared library calculations. And replicate either way — layout changes are silent, but a WINDOW_SUM witness column is not.

// Average order value at any view level (canonical INCLUDE)
AVG({ INCLUDE [Order ID] : SUM([Sales]) })

// Share of parent category (canonical EXCLUDE)
SUM([Sales]) / SUM({ EXCLUDE [Sub-Category] : SUM([Sales]) })

// Travel-safe FIXED equivalent: explicit grain, layout-proof
SUM([Sales]) / SUM({ FIXED [Category] : SUM([Sales]) })

Replicating Any LOD With Table Calculations

WINDOW_SUM is your independent witness. Because table calcs run after all filters at view grain, WINDOW_SUM(SUM([Sales])) always reflects exactly what the view shows. Place it beside any LOD total on a small filtered sample: agreement proves scope correct; divergence convicts the LOD's grain or context. No theory required — just two columns that must match.

Compute Using is the whole technique. Set it to the view's partitioning dimensions so the window covers the right scope; a misconfigured Compute Using makes the witness lie too. Start with Table (Across) on a simple crosstab, verify, then graduate to Specific Dimensions for complex layouts. When in doubt, show the Compute Using explicitly rather than trusting defaults.

Sampling keeps it fast. Replicate on a two-region, three-month slice — small enough to hand-verify, rich enough to expose grain errors. Hand-sum the visible rows on paper once; that thirty-second check has caught more scope bugs than any amount of staring at formulas.

Keep the witness permanently on a hidden validation sheet per workbook. Every future edit to the LOD, the view, or the filters re-runs the trial automatically: columns agree, ship it; columns diverge, investigate. Regression testing for dashboards costs one hidden sheet.

Table calcs can't replace LODs — they depend on view layout and vanish with dimensions — but as a verification instrument they're unmatched. The LOD declares intent; the table calc testifies to outcome. Ship only when both agree.

// Witness column: reflects exactly what the view shows
WINDOW_SUM(SUM([Sales]))
// Compute Using: Table (Across) on the validation crosstab

// Share check: LOD denominator vs witness must match
SUM({ FIXED [Region] : SUM([Sales]) })
WINDOW_SUM(SUM([Sales]), FIRST(), LAST())

Nested LODs and Measure Rollups: Counting Without Double-Counting

FIXED at fine grain beside coarse views repeats values: a customer total stamped on each of their orders. SUM over those repeats multiplies the truth by the row count — the classic LOD double-count. The rollup must match the grain: AVG, MIN, or MAX over repeated FIXED values, never SUM, unless you've deduplicated first.

Nested LODs layer grains deliberately: { FIXED [Region] : AVG({ FIXED [Customer ID] : SUM([Sales]) }) } averages customer totals within each region. The inner expression fixes customer grain; the outer aggregates those results at region grain. Read inside-out, verify inside-out: validate the inner LOD alone before trusting the outer average.

COUNTD inside LODs needs the same care. { FIXED [Region] : COUNTD([Customer ID]) } counts distinct customers per region correctly because the LOD groups before counting. But COUNTD over a blended or pre-aggregated source inherits that source's grain limits — distinct counts of summaries undercount, exactly as blending does.

Late-arriving filter dimensions inside FIXED declarations are a subtle trap: { FIXED [Region], [Segment] : SUM([Sales]) } hard-codes both dimensions, so a view filtered to one segment still totals correctly — but a view sliced by an unlisted third dimension repeats subgroup-blind values. List every grain dimension the question needs; omit none.

When nesting exceeds two levels, split the expression. Named intermediate fields — [Customer Total], then [Regional Avg of Customer Totals] — let you validate each layer's witness column independently. Readable LODs are debuggable LODs.

-- The double-count, in SQL terms: repeating a grouped value
-- across detail rows, then summing the repeats.
SELECT region,
       AVG(customer_total) AS avg_customer_sales,  -- correct rollup
       SUM(customer_total) AS inflated_double_count -- the trap
FROM (SELECT region, customer_id,
             SUM(sales) AS customer_total
      FROM orders
      GROUP BY region, customer_id) t
GROUP BY region;

A Scope Checklist Before Any LOD Ships

Every LOD deserves a five-line audit before it reaches a shared dashboard. One: name the grain in a comment — which dimensions, which population. Two: list every view filter and mark respect-or-ignore with context status to match. Three: replicate with WINDOW_SUM on a sample and record agreement. Four: toggle each filter and watch the total move or hold as designed. Five: state the rollup rule for repeated values.

Shared LOD libraries multiply the value. Keep canonical expressions — average order value, share of parent, cohort totals — in one documented workbook with their grain, context requirements, and witness sheets. New analyses copy from the library instead of reinventing scope, and fixes propagate from one maintained source.

Version your assumptions, not just formulas. When a FIXED denominator intentionally ignores Region for a company-wide benchmark, that intent belongs in a comment; otherwise the next analyst 'fixes' it into context and breaks the benchmark. Documented intent survives staff turnover.

Rehearse the failure modes with your team. Show them a frozen FIXED total, a collapsed EXCLUDE, a double-counted SUM — live, on real data. Analysts who've seen each failure once diagnose it in minutes forever. Scope literacy compounds across a team faster than any documentation.

Scope discipline is what separates analysts who build totals from analysts who build trusted totals. The engine always computes exactly what you declared. Make sure you declared what the business asked.