In Tableau, why does wrapping a FIXED LOD in SUM instead of AVG change the answer?

## An LOD is an aggregate that then gets aggregated Every level of detail expression returns an aggregate value computed at the grain it declares. That grain is rarely the same as the view's, and the view still needs exactly one number per mark. So Tableau applies a second, outer aggregation on top of the expression. When you drag the calculation onto a shelf as a measure, that outer aggregation defaults to SUM. The default is a convenience, not a recommendation, and treating it as invisible is the single most common cause of a wrong number involving LOD expressions. The expression and its wrapper are two separate decisions: ``` AVG({ FIXED [Customer ID] : SUM([Sales]) }) -- average sales per customer SUM({ FIXED [Customer ID] : SUM([Sales]) }) -- total sales MIN({ FIXED : SUM([Sales]) }) -- one whole-table constant ``` All three are valid, and only one of them answers any given question. ## Case 1: the expression is finer than the view A view broken out by Region, with an expression fixed at Customer ID. Each region mark covers many customers, so the mark has many per-customer values underneath it, and the outer aggregation genuinely has something to do: ``` Region customers per-customer totals SUM(...) AVG(...) East 3 40 / 30 / 30 100 33.3 West 2 50 / 30 80 40.0 ``` SUM returns the region's total sales; AVG returns the region's average sales per customer, which is a metric you cannot express with a plain aggregation at all. This is the case LOD expressions were designed for, and both wrappers are legitimate. ## Case 2: the expression is coarser than, or disjoint from, the view Now the opposite: an expression fixed at Region, displayed in a view broken out by Region *and* Category. The regional total has nothing finer to distinguish it, so the same value is replicated across every category mark in that region: ``` Region Category SUM(Sales) {FIXED [Region] : SUM([Sales])} East Furniture 40 100 East Tech 60 100 West Furniture 30 80 West Tech 50 80 ``` Reading down that last column and adding gives 360 against real sales of 180: the regional totals were counted once per category. Any place that adds those replicated values — a column total, a grand total, a summary card wrapping the field in SUM — inherits the inflation. The correct wrapper here is one that reads a constant back once: MIN, MAX, AVG or ATTR all return 100 for the East marks. `ATTR` is the most self-documenting because it returns an asterisk if the value is *not* constant within the mark, which is a useful alarm. ## Grand totals do not simply add the rows Tableau computes an automatic grand total by re-running the aggregation at the total's own level of detail, not by adding the values you can see. For a plain `SUM([Sales])` the two coincide, so nobody notices. For an average, or for a measure built on an LOD expression, they need not: the total row is a legitimate, independently computed number that happens to disagree with the arithmetic of the column above it. When someone reports "the total doesn't match", this is usually the mechanism, and the answer is to decide which number is the one the business wants rather than to force them to agree. ## How to diagnose it quickly - Duplicate the sheet and put the LOD's own dimensions in the view. If the number becomes correct, the expression is coarser than the original view and the wrapper is the problem. - Swap SUM for MIN and see whether the value stops moving. A constant that changes under SUM is a replicated value being added repeatedly. - Compare the mark-level values with the total. If the total is not the sum of what is shown, check whether the measure is an average or an LOD before assuming the data is wrong. ## Choosing the wrapper deliberately A usable rule: if the LOD grain is finer than the view, choose SUM or AVG according to the question you are answering. If the LOD grain is coarser than or unrelated to the view, choose MIN, MAX or ATTR so the constant is read once. If you want the number to follow the view instead of being pinned, consider INCLUDE or EXCLUDE rather than FIXED in the first place. ## What interviewers listen for They want you to say out loud that an LOD result is aggregated again, and that the default SUM is a choice. Strong candidates connect the wrapper to the relative grain of the expression and the view, name MIN or ATTR as the fix for a replicated constant, and know that a grand total is recomputed rather than added.