I have a measure that flags high value customers and sums their orders. Each row in the table looks right, but the total at the bottom is much smaller than adding the visible rows myself. Nobody at work seems surprised by this, which makes me feel like I am missing something obvious. Is this a bug in my DAX or is it supposed to work this way?
Other big source of the same symptom: DIVIDE. A ratio total is never the sum of the ratios, and it should not be. If you want a weighted total, sum the numerator and denominator separately and divide at the end.
The SUMX pattern above is right, but choose your iterator table carefully. Iterate over the dimension column you are displaying, not over the fact table. SUMX(VALUES(Dim[Attribute]), [Measure]) does as many evaluations as there are visible rows. SUMX(Fact, ...) does twelve million. Same answer, wildly different render time.
Learned this the hard way on a table with 400k SKUs. The measure was correct and the report was unusable.
Worth explaining this to your users out loud once, because they will find it before you do and quietly lose trust in the whole report. I keep a two-line note in a tooltip on any measure where rows and total legitimately differ.
Also check whether the table visual is hiding rows. If a customer has a blank measure value the row disappears entirely, so your manual addition of what is on screen was never going to match anyway.
@idempotent_ian · 5mo ago · 3 replies
Supposed to work that way, and it is the most common surprise in the whole language. The total row is not a sum of the rows above it. It is the same measure evaluated once more, in the filter context of the total — which is "all customers" rather than "this customer".
So if your measure is something like
IF([Order Value] > 1000, [Order Value]), at the customer level it asks whether this customer's total is over 1000, and at the grand total it asks whether everybody's total is over 1000, which is one giant number and one comparison. Rows and total are answering different questions.The fix is to force the evaluation down to the grain you mean:
SUMX(VALUES(Customer[CustomerKey]), IF([Order Value] > 1000, [Order Value])). Now the total genuinely is the sum of the per-customer answers.Reply
Report
@nadia_brill · 5mo ago
Worth adding a guard so the total is not misleading where it genuinely cannot be answered: wrap it in
IF(HASONEVALUE(Customer[CustomerKey]), ..., BLANK())and show a dash instead of a number someone will screenshot.Reply
Report
@late_stage_phd · 5mo ago
"The total is its own question" reframed the whole thing for me. I had been assuming a spreadsheet model where the bottom row is arithmetic on the rows above.
Reply
Report