DAX Grand Total Is Wrong? Why Totals Recalculate

A DAX grand total usually does not add the values displayed above it. Power BI evaluates the same measure again in the total cell’s broader filter context. That behavior is correct for ratios, distinct counts, thresholds, and many other non-additive calculations—but it can expose a mismatch between the measure’s grain and the business rule.

Why the total changes

Suppose a matrix has Product Category on rows:

Total Sales =
SUM ( Sales[Sales Amount] )

Each category row filters Product to one category. The grand total has no single-category row filter, so [Total Sales] evaluates across all visible categories. Because Sales Amount is additive, the result happens to equal the sum of the rows.

Now consider:

Margin % =
DIVIDE ( [Margin], [Total Sales] )

At the total, DAX divides total Margin by total Sales. It should not add category percentages. The same recalculation rule produces a different-looking but correct result.

The total is another query cell

Treat the total as a cell with its own filter context:

• report, page, and slicer filters still apply;
• the row grouping filter may be absent;
• cross-filters from other visuals may still apply; and
• the measure is evaluated from the beginning.

This mental model is more reliable than assuming the visual calculates detail first and adds it afterward.

Case 1: Ratios should recalculate

Conversion Rate =
DIVIDE ( [Converted Customers], [Eligible Customers] )

If Region A is 1 conversion out of 2 and Region B is 90 out of 100, adding row rates gives 140%. Averaging them gives 70%. The correct total is 91 divided by 102, or about 89.2%.

Ratios need totals of their components, not a sum of displayed percentages.

Case 2: Distinct counts should deduplicate

Customer Count =
DISTINCTCOUNT ( Sales[CustomerKey] )

A customer who buys in two categories appears in both category counts. At the grand total, that customer is counted once. The total can therefore be smaller than the sum of visible rows.

Forcing an iterator over categories would double-count shared customers. The DISTINCTCOUNT documentation notes that totals are not necessarily additive.

Case 3: Thresholds may need an explicit grain

Suppose the rule is:

Add sales for each product only when that product has at least 10,000 in sales.

This measure applies the threshold at the current cell:

Threshold Sales, Cell Grain =
IF ( [Total Sales] >= 10000, [Total Sales] )

At the grand total, combined sales probably exceed 10,000, so it returns all sales—even sales from products below the threshold.

The rule explicitly says “for each product,” so iterate Product:

Threshold Sales, Product Grain =
SUMX (
    VALUES ( Product[ProductKey] ),
    IF ( [Total Sales] >= 10000, [Total Sales] )
)

Now the threshold is tested once per visible product at every matrix level.

Case 4: A displayed-row total may be the actual requirement

Sometimes the business explicitly wants to add values at a reporting grain. For example, a monthly target may be stored as a non-additive snapshot, and the annual figure is defined as the sum of monthly targets.

Annual Target =
SUMX (
    VALUES ( 'Date'[Year Month] ),
    [Monthly Target]
)

This is valid because the requirement names Month as the grain. Do not substitute a visual-only function for that definition; write the measure so the intended grain is explicit.

How to diagnose a wrong total

Use this sequence before changing the formula.

1. State the business rule

Should the total:

• sum row values;
• recalculate a ratio;
• deduplicate entities;
• evaluate once per Product, Customer, or Month; or
• show no value at all?

“Make it match the visible rows” is not enough.

2. Expose the current scope

Debug Scope =
SWITCH (
    TRUE (),
    ISINSCOPE ( Product[Product Name] ), "Product",
    ISINSCOPE ( Product[Category] ), "Category",
    "Total"
)

ISINSCOPE helps identify the grouping level represented by the current cell.

3. Show intermediate measures

For a ratio, display numerator and denominator. For a threshold, display the entity count and qualifying entity count. For a distinct count, list a small set of shared keys.

4. Test a tiny dataset

Use two categories and three products. Include one customer in both categories or one product below the threshold. A deliberately chosen example makes the expected total obvious.

5. Add an iterator only when the grain requires it

SUMX(VALUES(...)) is not a generic total repair. It is a declaration: evaluate once per value of the named column and add those results.

Verification checklist

• Is the measure additive, semi-additive, or non-additive?
• Which grouping filter disappears at the total?
• Are entities shared across displayed rows?
• Does the business rule name an evaluation grain?
• Does an iterator use a stable key rather than a display label?
• Do subtotals and grand totals require the same behavior?
• Does a hand-calculated example match?

For a broader workflow, use the five DAX debugging habits. Then reproduce the case in the DAX playground and change one filter at a time.