DAX total rows: why summing percentages gives the wrong answer

A matrix total evaluates the measure under the total's filter context. It is not necessarily the arithmetic sum of the displayed row results. For ratios, recalculating from the total numerator and denominator is often exactly the desired behaviour.

Before trying to “fix” a total, define what the total should mean. Adding regional discount rates does not produce the overall discount rate, and averaging them equally ignores differences in eligible value.

Build a ratio from additive components

Use the retail lab and its baseline measures. Paid Gross Paise totals 72,000 and Paid Discount Paise totals 2,500.

Paid Discount Rate = DIVIDE([Paid Discount Paise], [Paid Gross Paise])

The overall expected rate is 2,500/72,000, approximately 3.4722%. Both components use the same Paid population and current date/product filters.

DIVIDE handles the denominator according to the rules documented in Microsoft's function reference. The business meaning comes from the component definitions, not the function name alone.

Inspect the regional rows

For the complete fixture, the relevant values are:

Adding the regional percentages gives 5.3191%, not the overall rate. Averaging the three displayed rates gives approximately 1.7730%, also wrong for the aggregate value-based rate.

The total is lower than North's rate because West and Unknown add eligible gross value without adding discount. That is a denominator effect, not a Power BI arithmetic bug.

Understand the total context

In a region row, the region filter restricts the numerator and denominator. At the overall total, that row-specific restriction is absent, while applicable report filters such as period remain.

The same measure therefore calculates 2,500/72,000 at the total. A SUMX over visible regional percentage results would instead add rates and answer a different question.

Sometimes a business explicitly wants a sum of independently calculated row outputs, such as certain allocation or threshold calculations. That needs its own definition and test. Do not apply a generic “sum visible rows” pattern to every measure whose total looks unfamiliar.

Check whether categories overlap

Even additive-looking counts can be nonadditive across groups. A customer buying both Software and Training appears in both category-specific customer counts but should appear once in an overall distinct-customer total.

The principle is the same: the total must follow the measure's definition over the total population. Summing displayed group results is valid only when the measure and group partition support that operation.

Keep category overlap and denominator weighting separate in your explanation. They are different reasons why a visible-row sum can be wrong.

Test filters and excluded members

Select January. Paid gross is 50,000 and discount 2,500, so the overall discount rate becomes 5%. The total should respond to the period while still recalculating the ratio correctly.

Exclude Unknown through a deliberate filter and the full-period denominator becomes 67,000, producing approximately 3.7313%. That result is valid for the narrower population, but it must not be labelled the complete paid business rate.

Display enough components for a reviewer to reproduce the ratio. A percentage-only matrix makes it harder to distinguish legitimate total behaviour from an eligibility error.

Use an independent arithmetic check

Sum eligible gross and discount amounts outside the visual, then divide. The fixture's Python/SQLite controls establish source amounts; DAX and matrix evaluation remain application-review steps.

Exercise: add a region with a very small gross value and a high discount rate. Compare the pooled ratio with an unweighted average of regional rates. Explain why the new small region should not have the same influence as a much larger region.

NeuraPath's Data Analytics with Generative AI course connects DAX totals with statistical and business reasoning. A correct total follows the measure's definition, even when it differs from adding the visible percentages.

Continue learning

This article is part of the DAX measures and analytical correctness sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Use DIVIDE to make undefined business ratios explicit.
• Continue with Build a distinct-customer measure with clear eligibility rules.