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. 👇

https://biinsight.com/wp-content/uploads/2025/10/Power-BIs-Broken-Totals-Myth-Debunked_01.mp3

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]) - 10

In 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

Each category (A and B) has three rows, and every row shows an amount of 100.
If we add them up, we get:

• Category A = 300
• Category B = 300
• Grand total = 600

Now imagine someone says, “let’s reduce each amount by 10.” That sounds simple, but it depends on what we really mean by “each amount.” Do we mean per item, per category, or once for the whole total? These three choices give us three different answers.

• Subtract 10 after adding everything together → 600 − 10 = 590
• Subtract 10 for each category → (300 − 10) + (300 − 10) = 580
• Subtract 10 for each row → (100 − 10) × 6 = 540

All of these are mathematically correct; they just happen at different levels. So, the answer to the “which one is correct?” is all of them are correct; it depends on “which one you actually wanted.”

Basic Math: Understanding the distributive property of arithmetic operations

Before we go further with the calculations, it helps to remember that arithmetic operations in maths have different properties. Each operation behaves in its own way when you combine it with addition or aggregation. Some operations keep the same result no matter when you apply them, while others change the outcome depending on the order or the level of detail.

Multiplication by a constant behaves consistently. This is known as the distributive property. It means you can multiply before or after you add things together, and the result will stay the same. Division is not always perfectly distributive in maths, but when dividing by a constant, it usually behaves the same way as multiplication. In most BI scenarios, both operations give consistent results across different levels of data.

Let’s use our SampleData table to make this simple.

Each category has three rows of 100, so both Category A and Category B total 300, and the grand total is 600.

Now imagine we want to apply a 10% discount (multiply by 0.9).

• If we apply it per row, then sum:
  (100 × 0.9) × 6 = 540
• If we apply it per group, then sum:
  (300 × 0.9) + (300 × 0.9) = 540
• If we apply it after summing everything:
  600 × 0.9 = 540

No matter how we do it, the total stays the same. That is because multiplication by a constant is distributive over addition.

But subtraction does not behave that way. Let’s subtract 10 instead:

• If we subtract 10 per row:
  (100 − 10) × 6 = 540
• If we subtract 10 per group:
  (300 − 10) + (300 − 10) = 580
• If we subtract 10 after adding everything:
  600 − 10 = 590

Now you can see the difference. The total depends on where the subtraction happens. Subtraction does not distribute evenly across addition, so the result changes with the calculation level.

This difference is very important in Power BI and other BI tools because the engine constantly aggregates, groups, and re-calculates measures at different levels of detail. When an operation is distributive, such as multiplication or division by a constant, the total remains consistent across levels. But for non-distributive operations like addition or subtraction, the total changes depending on the context (row, group, or total).

If you want to read more about the distributive property and other arithmetic properties, I believe Khan Academy: Distributive Property have done a great job explaining it.

Why this matters in BI tools

There is more to it than just basic maths. Most modern BI tools, such as Power BI, Tableau, and Excel when using the Data Model, are model driven. This means they rely on a semantic model that automatically adjusts calculations depending on the level of detail shown in the visual. The same formula is not fixed to one table or view; it is evaluated again for every row, group, and total, based on the current context.

So when you see a total in Power BI that looks different from what you expect, it is not because the tool is wrong. It is simply doing what it is designed to do, which is to re-evaluate your calculation at a broader level of detail. The model does not copy the values from the rows and add them up, it calculates the expression again in the context of the total.

This is where the distributive property we discussed earlier becomes important. Operations like multiplication and division by a constant work evenly across levels, so totals match up nicely. Addition and subtraction do not, which is why they often produce totals that surprise some users, especially the ones who come from an Excel background. Once you understand this behaviour, the results you see in Power BI, Tableau, and other model driven tools start to make complete sense.

Grouping and granularity

There is another layer that adds to the confusion. Many users mix up grouping and granularity, thinking they are the same thing when they are not.

• Granularity is the natural level of detail in your data. For example, each row in a sales table might represent one transaction or one item sold.
• Grouping is how the visual organises and presents that data, such as showing totals per category, per colour, per region, or per year.

When you group data, you are not changing the source data itself, you are only changing the scope in which your calculations happen.

So, if you subtract or add constants without considering granularity, you can easily shift the maths from “per item” to “per group” or even to “per total”.

Here is how you can think about it:

• If you mean “10 per item”, apply it before grouping.
• If you mean “10 per category”, apply it after grouping.
• If you mean “10 off the total”, apply it only at the total level.

Model driven tools like Power BI understand these scopes automatically and re-calculate the formula accordingly, but you must still write your measures carefully to match the intended granularity.

Additivity, semi-additivity, and non-additivity

There is yet another important concept that explains why totals do not always behave the same way. In his article, Diego Scalioni clearly describes three types of measures: additive, semi-additive, and non-additive. Understanding these helps to avoid a lot of confusion when reading totals in Power BI or any other BI tool.

• Additive measures, such as Sales Amount, can be safely summed across any dimension. Whether you add up sales by product, by region, or by month, the grand total will still make sense.
• Semi-additive measures, such as Account Balance, can be summed across some dimensions but not others. You can sum balances across accounts on the same date, because those balances exist at the same point in time, therefore the total makes sense. But you cannot sum balances across dates (like adding January + February + March balances) because that would be like adding three snapshots of the same money at different times, which does not make any mathematical or business sense.
• Non-additive measures, such as percentages or averages, should never be summed at all. They must be recalculated from totals at the level you are analysing. Many users think Power BI is wrong when totals for averages or ratios look strange. In reality, the measure is non-additive, so summing its results from each group gives the wrong picture. Therefore, summing non-additives itself is wrong, not the tool.

This misunderstanding is not specific to Power BI. The same logic applies in Tableau, Excel, and other analytical tools that work with grouped and aggregated data. Once you learn to identify whether a measure is additive, semi-additive, or non-additive, you start to understand exactly why the totals behave as they do.

Visual scope

There is still one more thing to understand before we move on. Every visual in a BI tool defines its own scope when it calculates numbers. Scope means the portion of data the visual is looking at when it runs a calculation (or a formula).

• Tables and matrices in Power BI re-calculate every single cell, subtotal, and total in its own filter context. The total row does not just add up the numbers from the rows above, it evaluates the same measure again in a wider scope that includes more data.
• Charts, like stacked bar charts, behave a bit differently. In most cases, the total you see is simply the sum of the visible series, not a full re-calculation of the measure.

That is why the same measure can show slightly different totals when you use it in a table versus a chart. It is not a bug or a mistake, it is just how each visual works to answer a different question. Tables focus on accuracy and context, while charts focus on comparison and readability. Understanding this helps you pick the right visual for the story you are trying to tell.

The same behaviour across tools

There is even more to learn when we look beyond Power BI. The same arithmetic logic appears in almost every analytical or reporting tool, even though each one applies it in a slightly different way.

Tableau

In Tableau, table calculations use partitioning to define how the calculation runs within parts of the data. Filters and Level of Detail (LOD) expressions then control the level of granularity, deciding which data Tableau includes in the calculation. Read more here.

This idea is quite close to Power BI’s filter context, which limits the data a measure sees, and in some cases, it behaves a bit like row context when working at a more detailed or fixed level of data.

• When you write SUM([Amount]) - 10, Tableau subtracts 10 once per partition, for example once per category or per colour depending on how you slice the data.
• When you write SUM([Amount] - 10), Tableau subtracts 10 for every row first and then sums the results.

The difference is exactly what we saw in the earlier section. Tableau lets you change this behaviour by adjusting the level of detail or using LOD expressions such as {FIXED [Category]: SUM([Amount])} - 10 if you want to control the calculation scope yourself.

The following screenshot shows the same sample data in Tableau as well as all the calculations:

As we see Tableau is doing a very similar thing. If the totals do not match with our expectation it doesn’t mean the tool is bboken or doing anything wrong.

Excel

In Excel, the behaviour depends on how you build your report.

• In cell based Excel, you decide everything. Each cell is independent and you can put the subtraction wherever you like. That is why users who come from Excel often find Power BI totals confusing because Excel cells do not use model context.
• In PivotTables, whether or not they use the Data Model, totals are re-aggregated at their own level. A PivotTable never just adds the visible rows; it calculates totals again based on the current grouping. When the PivotTable is connected to the Data Model (Power Pivot), the engine behind it is DAX, so its results match Power BI even more closely.

As you see in the image above, the total is 590! Now, I ask, Are Excel’s totals broken too? Well, I don’t think so.

Fun fact: I am not, and have never been an expert Excel user. NEVER! So if you look at the Pivot Table in the above image you notice that it does not have the Group Level SUM – 10 calculation. The reason is that I couldn’t easily figure out how to do it within the Pivot table itself and I did not want to add a column outside of the Pivot table. Perhaps, the expert Excel users reading this blog can guide me how to do that. 🙂

Paginated Reports

Paginated Reports work differently because they are query driven, not model driven. Prior to the latest features added to Microsoft Fabric in August 2025 where you can now create paginated reports online (directly within your web browser), you had to define a dataset first and then use expressions inside the report layout to aggregate values.

• Totals are author controlled. You can place a total at the detail level, group level or at the whole dataset level.
• The scope of the total depends entirely on how you design the report.

So if you want a total that subtracts 10 per row, you can write an expression like =Sum(Fields!Amount.Value - 10, "GroupName").
If you want to subtract 10 only once for the whole report, you use =Sum(Fields!Amount.Value, "DataSet1") - 10.

It is flexible but it also means consistency is your responsibility as the report designer.

If you use the online experience of Paginated Reports today (in Oct 2025), connect it to the semantic model created based on Greg’s sample data and create a new report, you will see that it behaves in the exact same way as the other tools behaved so far.

But what if I create the Paginated Report within the Power BI Report Builder?

Here is another screenshot of the Report Builder report run on my laptop:

Are the totals in the Report Builder also broken? Obviously not.

Did the tool automatically/magically detected how to handle the totals and subtotals in different group levels? Absolutely not. I wrote expressions telling the tool exactly how to handle the Subtotals and the Totals.

Here is a screenshot showing the Dataset definition in the Power BI Report Builder:

As you see, the tool is doing exactly what I asked it to do. So nothing is broken. Or is it? 👀

T-SQL

Finally, let’s look at how the same logic behaves in T-SQL, which shows these differences very clearly.

Start with the raw data:

SELECT Category, Colour, Amount
FROM SampleData;

If you want to get totals by category, you can write:

SELECT Category, SUM(Amount) AS [SUM]
FROM SampleData
GROUP BY Category;

At this point you are grouping by category, which is the same as visual grouping in Power BI or Tableau.

Now, if you also want to show the grand total together with the categories, you can use ROLLUP:

SELECT Category, SUM(Amount) AS [SUM]
FROM SampleData
GROUP BY ROLLUP(Category);

Here SQL automatically adds one more row that contains the grand total.

The same logic applies when you perform extra operations. For example, if you want to subtract 10 per row, you must put the subtraction inside the aggregation:

SELECT Category, SUM(Amount - 10) AS AdjustedTotal
FROM SampleData
GROUP BY ROLLUP(Category);

Here are the resutls:

If you instead subtract 10 outside the aggregation, like SUM(Amount) - 10, SQL will apply it once for the total, not for each row. So the results would be like this:

This example shows exactly the same concept as in Power BI, Tableau, Excel and Paginated Reports. The math itself does not change. What changes is how and when the operation is applied, which depends on the level of grouping and the scope of calculation defined by the tool.

Why a toggle would not fix it

There is a bit of temptation to think a simple “sum visible rows” toggle would solve everything. It sounds nice and easy, but in reality it would cause more problems than it fixes.

Power BI visuals are built to work correctly for all types of data, across every possible combination of filters, levels, and scopes. When you add a quick toggle, you are not just changing how the total looks, you are changing how the maths works.

Let’s think about it for a moment. If such a toggle existed, how would it handle these situations?

• What if the measure is semi additive or non additive, like an average or a percentage? Should it still just add visible rows?
• What if the visual has different groupings or uses a different granularity than the base data?
• What if the calculation depends on the visual scope, such as totals at the report level versus totals at the page level?

A simple toggle cannot account for all of these at once. It might make some totals look more “Excel like”, but at the cost of mathematical correctness.

Totals in Power BI and other model driven tools are designed to re-calculate measures in the right context. That is what keeps them consistent and accurate. If a toggle simply sums what you see, it would ignore the model and the relationships behind the numbers, giving users results that might look right but are actually wrong.

At the end of the day, it is the developer’s responsibility to define what they want to see in a visual. If you want totals to behave in a special way, you can write DAX that makes your intent clear. The visuals must stay consistent and reliable for every scenario, not just for one simplified case.

A better idea

There is always room to make things better, but the solution is not a quick toggle that hides the logic. A smarter and safer approach would be to give developers a clear and explicit way to define how totals should behave inside a visual.

Imagine if every visual had an optional “visual calculation for totals” field. Developers could write a simple expression that defines exactly how the total should be calculated for that measure in that visual. For example, one total could be written to sum per row, another to recalculate per group, or maybe even to show an adjusted average.

This idea keeps the maths honest while still giving flexibility. It respects the data model, follows context rules, and makes it obvious what each visual is doing.

It would also make Power BI easier to learn and teach. Instead of hiding complexity, it would make the logic visible. Users could see how totals are calculated, experiment safely, and understand the difference between additive and non additive behaviour.

So yes, improvements are always welcome, but they should empower developers to control totals clearly, not mask how the tool works underneath.

Wrapping it all up

Power BI totals are not broken. They work exactly how the DAX engine and the maths behind it tell them to. What sometimes feels confusing is really about not fully understanding context, grouping, granularity, and how additive or non additive measures behave.

Both Daniel Otykier’s post about context and Diego Scalioni’s article on additivity point to the same thing. The logic is solid and it also behaves the same way in other BI tools.

Adding a quick toggle would not really fix anything. It might make some totals look nice at first, but it will also make others wrong. The better way is to give developers clear control, maybe through something like a visual total calculation, similar to the visual calculations we already have but designed just for totals. That way, developers can decide exactly how totals should work while keeping the maths correct and transparent.

This post is not personal, it is scientific. It is about understanding how BI tools use maths and logic, not about who is right or wrong.

Greg’s persistence shows his passion for making Power BI easier for new users. Daniel’s response shows his attention to accuracy. Diego’s work connects the theory to practice. Together they started a valuable discussion that helps the whole Power BI community.

If this blog helps even one person understand why totals in Power BI behave the way they do, and why they are not broken, then I am happy.