Why your Power BI total is wrong (and how to fix it)

Sooner or later someone sends you a screenshot with a red circle around the Total row: “This doesn’t add up.” They’re right, and the report isn’t broken. It’s how Power BI totals work.

Totals are calculated, not added

A total row doesn’t sum the numbers above it. Power BI runs the same measure again, with the filter for the rows removed.

For SUM, that gives the same answer either way, so nobody notices. For anything else, like an average, a maximum, a distinct count or a ratio, the total answers a different question from the sum of the rows.

An example

This measure finds the sales of the best month:

Best Month Sales = MAXX(VALUES(Dates[Month Year]), [Total Sales])

In a matrix with Country on rows, each row shows that country’s best month. On Microsoft’s Financial Sample data:

The rows add up to 15,279,870.84, but the total shows 12,375,819.92. The total row has no country filter, so it finds the best month for all countries together. That’s a correct answer to a different question.

First, decide what the total should mean

Before writing any DAX, ask the person who reads the report. There are three honest options:

1. Keep it: the best month overall is useful. Rename the column so it’s clear.
2. Sum of the rows: they want each country’s best added up.
3. Blank: the total has no sensible meaning, so show nothing.

Fix 1: sum the rows with SUMX

To make the total the sum of the rows, iterate the rows yourself:

Best Month Fixed =
IF (
    HASONEVALUE ( financials[Country] ),
    [Best Month Sales],
    SUMX ( VALUES ( financials[Country] ), [Best Month Sales] )
)

HASONEVALUE is TRUE on a country row (one country is filtered) and FALSE on the total. On the total, SUMX runs the measure once per country and adds the results.

Fix 2: hide the total with ISINSCOPE

Best Month (rows only) =
IF ( ISINSCOPE ( financials[Country] ), [Best Month Sales] )

ISINSCOPE is TRUE only when the visual is grouping by that column at this level. On the total it’s FALSE, so the measure returns blank.

HASONEVALUE or ISINSCOPE?

They behave differently when a slicer picks one country:

With a slicer on one value, HASONEVALUE sees one country and treats the total like a row. ISINSCOPE only checks the visual’s grouping, so it’s the safer choice in matrices, and essential when there are several levels (Region, then Country).

Don’t just turn totals off

You can switch totals off in the Format pane, and sometimes that’s right. But if the number is useful, a clear measure is better than a missing one. Readers trust a report more when the totals mean something they can explain.

Try it

The totals lesson has you build the matrix above, spot the wrong total and fix it, with instant checks.

Sources

• HASONEVALUE function, Microsoft Learn
• ISINSCOPE function, Microsoft Learn
• Create a matrix visual in Power BI, Microsoft Learn