The long-running debate around how Power BI calculates totals in tables and matrices has been part of the community conversation for years. Greg Deckler has kept the topic alive through his ongoing “broken totals” posts on social media, often suggesting that Power BI should include a simple toggle to make totals behave more like Excel. His continued campaign prompted a detailed reply from Daniel Otykier in his article “No More Measure Totals Shenanigans“, and earlier, Diego Scalioni explored how DAX evaluates totals internally in his post “Cache me if you can: DAX Totals behind the scenes“.
This blog brings all those perspectives together from a scientific and comparative angle. It looks at how totals are calculated in Power BI and compares that behaviour with Tableau, Excel, Paginated Reports, and even T-SQL. The goal is not to take sides, but to clear up the confusion around what is happening under the hood.
If you are into podcasts and prefer the audio version of this blog, I got you covered. Here an AI generated podcast for this blog. 👇
Are Power BI Totals Really Broken?
Let’s get one thing clear right at the start, no, Power BI totals are not broken. There is no “it depends” this time. What some interpret as broken behaviour is actually how DAX and the underlying model are designed to work.
This post is not personal, it is purely scientific and technical. While I have great respect for Greg and his significant contributions to the Power BI community, I disagree with the use of the word “BROKEN.” It sounds dramatic but does not reflect the full truth. Totals in Power BI behave exactly as the model and the maths define them to. Want to know why? Keep reading.
Why this matters
When someone with Greg’s influence keeps saying totals are “broken”, it really affects how new users see Power BI. Some even start thinking the tool itself is not reliable, when what they are seeing is actually how different reporting tools do their calculations in different ways.
It helps to know the main calculation styles that these tools use:
- Cell based: This is what you get in worksheet formulas and classic PivotTables that use Excel ranges. Totals are just simple sums of the shown items, with no model or relationships behind the scene.
- Model driven: This is how Power BI works and also Excel PivotTables that use the Data Model (Power Pivot) or connect to a tabular dataset. Measures are calculated again for every context, so totals depend on how filters and relationships are set.
- Query driven: Tools like Paginated Reports work this way. The report runs a query, for example SQL or DAX, gets the dataset, and then sums or averages values in the report design. The author decides how each total should be calculated.
- Hybrid (query and context driven): Tableau fits in here. It gets the data through a query but also lets you change the level of detail and how totals behave in the visual. So sometimes it acts like a query tool and sometimes more like a model one.
Most of the confusion happens when people compare results from these tools as if they all worked the same way. Once you understand the difference between cell based, model driven, query driven, and hybrid tools, the way Power BI shows its totals starts to make full sense.
The problem that started it
Greg’s long-running example uses a small table with a single column of numbers and a DAX measure like this:
SUMX(SampleData, SampleData[Amount]) - 10In the total row, the result shows 590, while he expects 580 (two groups of 290 each). Based on that, he argues that Power BI totals are “wrong”.
But DAX is only doing what it is told to do. In this measure, the subtraction of 10 happens after the total amount is calculated, not for each row. If the intention was to take 10 away per row, then the measure should be written like this:
SUMX(SampleData, SampleData[Amount] - 10)This version gives the expected 580 because the subtraction now happens at the lowest level of detail, which is per row.
This might look like a small detail, but it is exactly where most of the confusion around totals begins. The difference is not about Power BI being wrong; it is about understanding where in the calculation the operation happens.
The math behind it
Before we look at the numbers, let’s first talk about what we are trying to do. We Greg’s small and very simple table that shows some amounts by Category and Colour:
| Category | Colour | Amount |
|---|---|---|
| A | Red | 100 |
| A | Green | 100 |
| A | Blue | 100 |
| B | Red | 100 |
| B | Green | 100 |
| B | Blue | 100 |
