In a Power BI matrix, why doesn't a DAX measure's total row equal the sum of the rows?

## Nothing is being summed The mental model that causes this question is that a visual computes cells and then adds them for the total. It does not. Power BI issues an evaluation of the measure per cell, each in the filter context that cell carries. The total row is one more cell whose context happens to omit the filter that the row header supplied. The measure has no idea it is "the total" and no access to the other cells' results. For an additive measure the arithmetic coincides. `SUM ( Sales[Amount] )` over all categories equals the sum of the per-category sums, because addition partitions cleanly. That coincidence trains everyone to expect summation, and then a ratio breaks it. ## The three families that break **Ratios.** `Margin % = DIVIDE ( [Profit], [Sales] )` at the total is total profit over total sales — the correct blended margin. Rows of 20%, 40% and 15% do not add to 75%, and nobody wants them to. Here the recomputed total is the *right* answer and the reader's expectation is the thing that is wrong; you fix it with formatting and labelling, not with DAX. **Distinct counts.** `DISTINCTCOUNT ( Sales[CustomerKey] )` per category counts customers within that category; the total counts distinct customers overall. A customer who bought in three categories is counted three times down the rows and once in the total, so the total is legitimately smaller. This one surprises business readers every time, and the honest answer is a footnote explaining that customers overlap. **Conditionals and thresholds.** `IF ( [Sales] > 100000, [Bonus], 0 )` evaluates the condition once at the total grain, where sales are large, so the total can pay a bonus even though every individual row failed the test — or the reverse. Same for `MAX`, `MIN`, ranking-based and "top N" logic. This family is where the total is genuinely misleading and DAX has to be changed. ## Making the total the sum of the rows When the per-row result really is the atom you want added, iterate the grain explicitly: ``` Bonus Total = SUMX ( VALUES ( Product[Category] ), [Bonus] ) ``` `VALUES ( Product[Category] )` returns the categories visible in the current filter context — one row per category on the total, a single row inside a category cell — and the measure reference triggers context transition, so `[Bonus]` is evaluated per category exactly as the row cells did. On a row cell the iteration has one member and the result is unchanged; on the total it is the sum you wanted. Note that this fixes the total *for that grain only*; a matrix with two nested levels needs the iteration to match whichever level is in scope. ## Branching on what is in scope The other tool is to detect the total explicitly. `ISINSCOPE ( Product[Category] )` returns TRUE when that column is grouping the current cell, so: ``` Bonus Display = IF ( ISINSCOPE ( Product[Category] ), [Bonus], SUMX ( VALUES ( Product[Category] ), [Bonus] ) ) ``` `HASONEVALUE ( Product[Category] )` is the older test and is nearly equivalent, but it also returns TRUE when a slicer happens to have narrowed the column to one member, which ISINSCOPE does not — that difference is what makes ISINSCOPE the safer choice for total detection. You can also return `BLANK()` at the total when no total is meaningful, which suppresses the number rather than showing a misleading one. ## Diagnosing it in the wild When someone reports a wrong total, the sequence is: identify whether the measure is additive; if it is, look for a duplicated or fanned-out relationship path rather than a total problem; if it is not, decide which of the three families it belongs to and whether the recomputed total is actually correct. Half the tickets close with an explanation and a tooltip. The other half close with a `SUMX ( VALUES ( … ) )` or an `ISINSCOPE` branch. ## What separates a senior answer A junior says "totals recompute". A senior says which measures recompute *differently*, names one case where the recomputed total is right and the expectation is wrong, gives the SUMX-over-VALUES fix with the grain caveat, and knows the ISINSCOPE-versus-HASONEVALUE distinction. It is a diagnosis question dressed as a trivia question.