The long-running debate round how Energy BI calculates totals in tables and matrices has been a part of the group dialog for years. Greg Deckler has saved the subject alive via his ongoing “damaged totals” posts on social media, usually suggesting that Energy BI ought to embrace a easy toggle to make totals behave extra like Excel. His continued marketing campaign prompted an in depth reply from Daniel Otykier in his article “No Extra Measure Totals Shenanigans“, and earlier, Diego Scalioni explored how DAX evaluates totals internally in his publish “Cache me if you happen to can: DAX Totals behind the scenes“.

This weblog brings all these views collectively from a scientific and comparative angle. It appears at how totals are calculated in Energy BI and compares that behaviour with Tableau, Excel, Paginated Reviews, and even T-SQL. The purpose is to not take sides, however to clear up the confusion round what is going on underneath the hood.

Let’s get one factor clear proper at the beginning, no, Energy BI totals are not damaged. There isn’t a “it relies upon” this time. What some interpret as damaged behaviour is definitely how DAX and the underlying mannequin are designed to work.

This publish shouldn’t be private, it’s purely scientific and technical. Whereas I’ve nice respect for Greg and his vital contributions to the Energy BI group, I disagree with using the phrase “BROKEN.” It sounds dramatic however doesn’t replicate the complete reality. Totals in Energy BI behave precisely because the mannequin and the maths outline them to. Wish to know why? Preserve studying.

Why this issues

When somebody with Greg’s affect retains saying totals are “damaged”, it actually impacts how new customers see Energy BI. Some even begin considering the device itself shouldn’t be dependable, when what they’re seeing is definitely how completely different reporting instruments do their calculations in several methods.

It helps to know the primary calculation types that these instruments use:

• Cell primarily based: That is what you get in worksheet formulation and traditional PivotTables that use Excel ranges. Totals are simply easy sums of the proven objects, with no mannequin or relationships behind the scene.
• Mannequin pushed: That is how Energy BI works and in addition Excel PivotTables that use the Knowledge Mannequin (Energy Pivot) or connect with a tabular dataset. Measures are calculated once more for each context, so totals rely upon how filters and relationships are set.
• Question pushed: Instruments like Paginated Reviews work this manner. The report runs a question, for instance SQL or DAX, will get the dataset, after which sums or averages values within the report design. The creator decides how every complete needs to be calculated.
• Hybrid (question and context pushed): Tableau matches in right here. It will get the info via a question but additionally enables you to change the extent of element and the way totals behave within the visible. So generally it acts like a question device and generally extra like a mannequin one.

A lot of the confusion occurs when individuals examine outcomes from these instruments as if all of them labored the identical manner. When you perceive the distinction between cell primarily based, mannequin pushed, question pushed, and hybrid instruments, the way in which Energy BI exhibits its totals begins to make full sense.

The issue that began it

Greg’s long-running instance makes use of a small desk with a single column of numbers and a DAX measure like this:

SUMX(SampleData, SampleData[Amount]) - 10

Within the complete row, the consequence exhibits 590, whereas he expects 580 (two teams of 290 every). Primarily based on that, he argues that Energy BI totals are “flawed”.

However DAX is barely doing what it’s informed to do. On this measure, the subtraction of 10 occurs after the whole quantity is calculated, not for every row. If the intention was to take 10 away per row, then the measure needs to be written like this:

SUMX(SampleData, SampleData[Amount] - 10)

This model offers the anticipated 580 as a result of the subtraction now occurs on the lowest degree of element, which is per row.

This may appear like a small element, however it’s precisely the place a lot of the confusion round totals begins. The distinction shouldn’t be about Energy BI being flawed; it’s about understanding the place within the calculation the operation occurs.

The mathematics behind it

Earlier than we take a look at the numbers, let’s first discuss what we try to do. We Greg’s small and quite simple desk that exhibits some quantities by Class and Color:

Every class (A and B) has three rows, and each row exhibits an quantity of 100.
If we add them up, we get:

• Class A = 300
• Class B = 300
• Grand complete = 600

Now think about somebody says, “let’s cut back every quantity by 10.” That sounds easy, nevertheless it relies on what we actually imply by “every quantity.” Will we imply per merchandise, per class, or as soon as for the entire complete? These three decisions give us three completely different solutions.

• Subtract 10 after including every little thing collectively → 600 − 10 = 590
• Subtract 10 for every class → (300 − 10) + (300 − 10) = 580
• Subtract 10 for every row → (100 − 10) × 6 = 540

All of those are mathematically right; they simply occur at completely different ranges. So, the reply to the “which one is right?” is all of them are right; it relies on “which one you truly wished.”

Primary Math: Understanding the distributive property of arithmetic operations

Earlier than we go additional with the calculations, it helps to do not forget that arithmetic operations in maths have completely different properties. Every operation behaves in its personal manner while you mix it with addition or aggregation. Some operations preserve the identical consequence irrespective of while you apply them, whereas others change the result relying on the order or the extent of element.

Multiplication by a relentless behaves constantly. This is named the distributive property. It means you’ll be able to multiply earlier than or after you add issues collectively, and the consequence will keep the identical. Division shouldn’t be at all times completely distributive in maths, however when dividing by a relentless, it often behaves the identical manner as multiplication. In most BI eventualities, each operations give constant outcomes throughout completely different ranges of knowledge.

Let’s use our SampleData desk to make this straightforward.

Every class has three rows of 100, so each Class A and Class B complete 300, and the grand complete is 600.

Now think about we need to apply a ten% low cost (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 every little thing:
  600 × 0.9 = 540

Regardless of how we do it, the whole stays the identical. That’s as a result of multiplication by a relentless is distributive over addition.

However subtraction doesn’t behave that manner. Let’s subtract 10 as an alternative:

• 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 including every little thing:
  600 − 10 = 590

Now you’ll be able to see the distinction. The entire relies on the place the subtraction occurs. Subtraction doesn’t distribute evenly throughout addition, so the consequence adjustments with the calculation degree.

This distinction is essential in Energy BI and different BI instruments as a result of the engine continually aggregates, teams, and re-calculates measures at completely different ranges of element. When an operation is distributive, corresponding to multiplication or division by a relentless, the whole stays constant throughout ranges. However for non-distributive operations like addition or subtraction, the whole adjustments relying on the context (row, group, or complete).

If you wish to learn extra in regards to the distributive property and different arithmetic properties, I consider Khan Academy: Distributive Property have achieved an important job explaining it.

Why this issues in BI instruments

There’s extra to it than simply primary maths. Most trendy BI instruments, corresponding to Energy BI, Tableau, and Excel when utilizing the Knowledge Mannequin, are mannequin pushed. This implies they depend on a semantic mannequin that mechanically adjusts calculations relying on the extent of element proven within the visible. The identical formulation shouldn’t be fastened to 1 desk or view; it’s evaluated once more for each row, group, and complete, primarily based on the present context.

So while you see a complete in Energy BI that appears completely different from what you count on, it’s not as a result of the device is flawed. It’s merely doing what it’s designed to do, which is to re-evaluate your calculation at a broader degree of element. The mannequin doesn’t copy the values from the rows and add them up, it calculates the expression once more within the context of the complete.

That is the place the distributive property we mentioned earlier turns into essential. Operations like multiplication and division by a relentless work evenly throughout ranges, so totals match up properly. Addition and subtraction don’t, which is why they usually produce totals that shock some customers, particularly those who come from an Excel background. When you perceive this behaviour, the outcomes you see in Energy BI, Tableau, and different mannequin pushed instruments begin to make full sense.

Grouping and granularity

There’s one other layer that provides to the confusion. Many customers combine up grouping and granularity, considering they’re the identical factor when they don’t seem to be.

• Granularity is the pure degree of element in your knowledge. For instance, every row in a gross sales desk may symbolize one transaction or one merchandise offered.
• Grouping is how the visible organises and presents that knowledge, corresponding to exhibiting totals per class, per color, per area, or per 12 months.

Whenever you group knowledge, you aren’t altering the supply knowledge itself, you might be solely altering the scope by which your calculations occur.

So, if you happen to subtract or add constants with out contemplating granularity, you’ll be able to simply shift the maths from “per merchandise” to “per group” and even to “per complete”.

Right here is how one can give it some thought:

• In the event you imply “10 per merchandise”, apply it earlier than grouping.
• In the event you imply “10 per class”, apply it after grouping.
• In the event you imply “10 off the whole”, apply it solely on the complete degree.

Mannequin pushed instruments like Energy BI perceive these scopes mechanically and re-calculate the formulation accordingly, however you will need to nonetheless write your measures fastidiously to match the supposed granularity.

Additivity, semi-additivity, and non-additivity

There’s yet one more essential idea that explains why totals don’t at all times behave the identical manner. In his article, Diego Scalioni clearly describes three forms of measures: additive, semi-additive, and non-additive. Understanding these helps to keep away from plenty of confusion when studying totals in Energy BI or every other BI device.

• Additive measures, corresponding to Gross sales Quantity, may be safely summed throughout any dimension. Whether or not you add up gross sales by product, by area, or by month, the grand complete will nonetheless make sense.
• Semi-additive measures, corresponding to Account Steadiness, may be summed throughout some dimensions however not others. You may sum balances throughout accounts on the identical date, as a result of these balances exist on the similar cut-off date, subsequently the whole is smart. However you can not sum balances throughout dates (like including January + February + March balances) as a result of that might be like including three snapshots of the similar cash at completely different occasions, which doesn’t make any mathematical or enterprise sense.
• Non-additive measures, corresponding to percentages or averages, ought to by no means be summed in any respect. They should be recalculated from totals on the degree you might be analysing. Many customers assume Energy BI is flawed when totals for averages or ratios look unusual. In actuality, the measure is non-additive, so summing its outcomes from every group offers the flawed image. Subsequently, summing non-additives itself is flawed, not the device.

This misunderstanding shouldn’t be particular to Energy BI. The identical logic applies in Tableau, Excel, and different analytical instruments that work with grouped and aggregated knowledge. When you study to establish whether or not a measure is additive, semi-additive, or non-additive, you begin to perceive precisely why the totals behave as they do.

Visible scope

There’s nonetheless another factor to grasp earlier than we transfer on. Each visible in a BI device defines its personal scope when it calculates numbers. Scope means the portion of knowledge the visible is when it runs a calculation (or a formulation).

• Tables and matrices in Energy BI re-calculate each single cell, subtotal, and complete in its personal filter context. The entire row doesn’t simply add up the numbers from the rows above, it evaluates the identical measure once more in a wider scope that features extra knowledge.
• Charts, like stacked bar charts, behave a bit in a different way. Normally, the whole you see is just the sum of the seen sequence, not a full re-calculation of the measure.

That’s the reason the identical measure can present barely completely different totals while you use it in a desk versus a chart. It’s not a bug or a mistake, it’s simply how every visible works to reply a unique query. Tables give attention to accuracy and context, whereas charts give attention to comparability and readability. Understanding this helps you decide the best visible for the story you are attempting to inform.

The identical behaviour throughout instruments

There’s much more to study once we look past Energy BI. The identical arithmetic logic seems in nearly each analytical or reporting device, though each applies it in a barely completely different manner.

Tableau

In Tableau, desk calculations use partitioning to outline how the calculation runs inside elements of the info. Filters and Degree of Element (LOD) expressions then management the extent of granularity, deciding which knowledge Tableau consists of within the calculation. Learn extra right here.

This concept is sort of near Energy BI’s filter context, which limits the info a measure sees, and in some instances, it behaves a bit like row context when working at a extra detailed or fastened degree of knowledge.

• Whenever you write SUM([Amount]) - 10, Tableau subtracts 10 as soon as per partition, for instance as soon as per class or per color relying on the way you slice the info.
• Whenever you write SUM([Amount] - 10), Tableau subtracts 10 for each row first after which sums the outcomes.

The distinction is strictly what we noticed within the earlier part. Tableau enables you to change this behaviour by adjusting the extent of element or utilizing LOD expressions corresponding to {FIXED [Category]: SUM([Amount])} - 10 if you wish to management the calculation scope your self.

The next screenshot exhibits the identical pattern knowledge in Tableau in addition to all of the calculations:

As we see Tableau is doing a really related factor. If the totals don’t match with our expectation it doesn’t imply the device is bboken or doing something flawed.

Excel

In Excel, the behaviour relies on the way you construct your report.

• In cell primarily based Excel, you resolve every little thing. Every cell is impartial and you’ll put the subtraction wherever you want. That’s the reason customers who come from Excel usually discover Energy BI totals complicated as a result of Excel cells don’t use mannequin context.
• In PivotTables, whether or not or not they use the Knowledge Mannequin, totals are re-aggregated at their very own degree. A PivotTable by no means simply provides the seen rows; it calculates totals once more primarily based on the present grouping. When the PivotTable is related to the Knowledge Mannequin (Energy Pivot), the engine behind it’s DAX, so its outcomes match Energy BI much more carefully.

As you see within the picture above, the whole is 590! Now, I ask, Are Excel’s totals damaged too? Effectively, I don’t assume so.

Enjoyable truth: I’m not, and have by no means been an skilled Excel person. NEVER! So if you happen to take a look at the Pivot Desk within the above picture you discover that it doesn’t have the Group Degree SUM – 10 calculation. The reason being that I couldn’t simply determine methods to do it throughout the Pivot desk itself and I didn’t need to add a column outdoors of the Pivot desk. Maybe, the skilled Excel customers studying this weblog can information me how to do this. 🙂

Paginated Reviews

Paginated Reviews work in a different way as a result of they’re question pushed, not mannequin pushed. Previous to the newest options added to Microsoft Material in August 2025 the place now you can create paginated reviews on-line (instantly inside your internet browser), you needed to outline a dataset first after which use expressions contained in the report format to mixture values.

• Totals are creator managed. You may place a complete on the element degree, group degree or on the complete dataset degree.
• The scope of the whole relies upon totally on the way you design the report.

So if you would like a complete that subtracts 10 per row, you’ll be able to write an expression like =Sum(Fields!Quantity.Worth - 10, "GroupName").
If you wish to subtract 10 solely as soon as for the entire report, you utilize =Sum(Fields!Quantity.Worth, "DataSet1") - 10.

It’s versatile nevertheless it additionally means consistency is your accountability because the report designer.

In the event you use the web expertise of Paginated Reviews right now (in Oct 2025), join it to the semantic mannequin created primarily based on Greg’s pattern knowledge and create a brand new report, you will notice that it behaves in the very same manner as the opposite instruments behaved to this point.

However what if I create the Paginated Report throughout the Energy BI Report Builder?

Right here is one other screenshot of the Report Builder report run on my laptop computer:

Are the totals within the Report Builder additionally damaged? Clearly not.

Did the device mechanically/magically detected methods to deal with the totals and subtotals in several group ranges? Completely not. I wrote expressions telling the device precisely methods to deal with the Subtotals and the Totals.

Here’s a screenshot exhibiting the Dataset definition within the Energy BI Report Builder:

As you see, the device is doing precisely what I requested it to do. So nothing is damaged. Or is it? 👀

T-SQL

Lastly, let’s take a look at how the identical logic behaves in T-SQL, which exhibits these variations very clearly.

Begin with the uncooked knowledge:

SELECT Class, Color, Quantity
FROM SampleData;

If you wish to get totals by class, you’ll be able to write:

SELECT Class, SUM(Quantity) AS [SUM]
FROM SampleData
GROUP BY Class;

At this level you might be grouping by class, which is similar as visible grouping in Energy BI or Tableau.

Now, if you happen to additionally need to present the grand complete along with the classes, you should use ROLLUP:

SELECT Class, SUM(Quantity) AS [SUM]
FROM SampleData
GROUP BY ROLLUP(Class);

Right here SQL mechanically provides another row that incorporates the grand complete.

The identical logic applies while you carry out further operations. For instance, if you wish to subtract 10 per row, you will need to put the subtraction contained in the aggregation:

SELECT Class, SUM(Quantity - 10) AS AdjustedTotal
FROM SampleData
GROUP BY ROLLUP(Class);

Listed here are the resutls:

In the event you as an alternative subtract 10 outdoors the aggregation, like SUM(Quantity) - 10, SQL will apply it as soon as for the whole, not for every row. So the outcomes could be like this:

This instance exhibits precisely the identical idea as in Energy BI, Tableau, Excel and Paginated Reviews. The mathematics itself doesn’t change. What adjustments is how and when the operation is utilized, which relies on the extent of grouping and the scope of calculation outlined by the device.

Why a toggle wouldn’t repair it

There’s a little bit of temptation to assume a easy “sum seen rows” toggle would clear up every little thing. It sounds good and straightforward, however in actuality it could trigger extra issues than it fixes.

Energy BI visuals are constructed to work appropriately for every type of knowledge, throughout each doable mixture of filters, ranges, and scopes. Whenever you add a fast toggle, you aren’t simply altering how the whole appears, you might be altering how the maths works.

Let’s give it some thought for a second. If such a toggle existed, how wouldn’t it deal with these conditions?

• What if the measure is semi additive or non additive, like a mean or a share? Ought to it nonetheless simply add seen rows?
• What if the visible has completely different groupings or makes use of a unique granularity than the bottom knowledge?
• What if the calculation relies on the visible scope, corresponding to totals on the report degree versus totals on the web page degree?

A easy toggle can’t account for all of those without delay. It’d make some totals look extra “Excel like”, however at the price of mathematical correctness.

Totals in Energy BI and different mannequin pushed instruments are designed to re-calculate measures in the best context. That’s what retains them constant and correct. If a toggle merely sums what you see, it could ignore the mannequin and the relationships behind the numbers, giving customers outcomes that may look proper however are literally flawed.

On the finish of the day, it’s the developer’s accountability to outline what they need to see in a visible. If you need totals to behave in a particular manner, you’ll be able to write DAX that makes your intent clear. The visuals should keep constant and dependable for each situation, not only for one simplified case.

A greater thought

There’s at all times room to make issues higher, however the resolution shouldn’t be a fast toggle that hides the logic. A wiser and safer method could be to present builders a transparent and express technique to outline how totals ought to behave inside a visible.

Think about if each visible had an elective “visible calculation for totals” discipline. Builders might write a easy expression that defines precisely how the whole needs to be calculated for that measure in that visible. For instance, one complete might be written to sum per row, one other to recalculate per group, or possibly even to point out an adjusted common.

This concept retains the maths sincere whereas nonetheless giving flexibility. It respects the info mannequin, follows context guidelines, and makes it apparent what every visible is doing.

It could additionally make Energy BI simpler to study and educate. As a substitute of hiding complexity, it could make the logic seen. Customers might see how totals are calculated, experiment safely, and perceive the distinction between additive and non additive behaviour.

So sure, enhancements are at all times welcome, however they need to empower builders to manage totals clearly, not masks how the device works beneath.

Wrapping all of it up

Energy BI totals are usually not damaged. They work precisely how the DAX engine and the maths behind it inform them to. What generally feels complicated is admittedly about not absolutely understanding context, grouping, granularity, and the way additive or non additive measures behave.

Each Daniel Otykier’s publish about context and Diego Scalioni’s article on additivity level to the identical factor. The logic is stable and it additionally behaves the identical manner in different BI instruments.

Including a fast toggle would probably not repair something. It’d make some totals look good at first, however it can additionally make others flawed. The higher manner is to present builders clear management, possibly via one thing like a visible complete calculation, much like the visible calculations we have already got however designed only for totals. That manner, builders can resolve precisely how totals ought to work whereas retaining the maths right and clear.

This publish shouldn’t be private, it’s scientific. It’s about understanding how BI instruments use maths and logic, not about who is correct or flawed.

Greg’s persistence exhibits his ardour for making Energy BI simpler for brand spanking new customers. Daniel’s response exhibits his consideration to accuracy. Diego’s work connects the speculation to apply. Collectively they began a precious dialogue that helps the entire Energy BI group.

If this weblog helps even one particular person perceive why totals in Energy BI behave the way in which they do, and why they don’t seem to be damaged, then I’m glad.