Why Power BI totals might seem wrong

A common question is why Power BI totals are inaccurate because they do not display the sum of individual rows. In this video, we explain the reasons why those totals are correct.
Article and download: sql.bi/772093?aff=yt
How to learn DAX: www.sqlbi.com/guides/dax/?aff=yt
The definitive guide to DAX: www.sqlbi.com/books/the-defin...

[@matthiask4602]

"...and that is totally insane." LOL, you did it again, Alberto, such a good teacher. This video is an eyeopener for all DAX newbies.

[@Milhouse77BS]

at 20:19 lol.

[@Darkzos]

I'm in my first job as Data Analyst and the task that they left me was to show some totals on the measures, I was struggling almost 2 weeks watching different videos until I reach this and fix it in a day, thanks! You gain a new subscriber and surely I'll be watching all your videos!.

[@BummerSlug]

I appreciate that you always seem to have the answers to the question I was still trying to work out. SMH - I’ve been banging my head on my desk trying to figure out why my numbers are wrong.

[@mwaltercpa]

This is a half hour well spent. Thank you for working through all the different non-additive examples. Makes perfect sense that there are non-additive measures (and totals) for a good reason.

[@goldwhispers]

totally understand what you saying and I think for developers this is important, i still think it would be good if there was a way to enable it manually to just sum the total rows. However my other feeling is around this complex aspect of dax seems to contradict the whole self service aspect of Power BI that users should be able to just 'play and play' sort of thing. Understanding how totals work requires quite a bit of knowledge and understanding and therefore for Microsoft to promote power bi as a plug and play self service tool its not entirely true as it requires experienced modellers to sit between the data a to create a self service tool so to speak. Not that I am complaining I love my job :)

[@etherlords88]

SUMX( VALUES(Table[Column]) ,Expression/Calculation/Measure ) worked for me! THANKS!!! This was pain in my bum for weeks!

[@mazchen]

Apart from the great content (as usual) loving the new beard :-) After two days of desperately trying to summarize products of columns in two tables (for a linear regression model), you helped me to understand why the totals didn't work within the first 15 mins of a video! I just don't understand why this channel is so underrated....

[@Pegasusjumpingoff]

omg thank you very much a small solution that resolves my headache for half a week

[@ozansen_ozibaba]

The Father of DAX! I have never thought like this about additivity vs nonadditivity in simple aggregate calculations like AVG and SUM! Impressive yet suprising!

[@joeyCrxck]

Great video - I had a customer ask me if I was counting or summing a value twice because they do not understand additivity and non-additivity. I now create their visuals with forced additivity. It's a hassle but worth not having all the questions anymore

[@pro1427]

AMAZING, i was hitting my head on the wall over the DISTINCTCOUNT problem for hous, now i understand the problem. Thanks!!

[@fahadnazir8080]

Love you for your videos!!!! The way you articulate complex situations in such a simple way is comendable!

[@davidirias3459]

This has being my current problem in a reporte which the grandtotal change dramatically when I change a filter, I'm very grateful with you, Alberto. Regards!

[@padynz9869]

Excellent exhaustive video with impeccable clarity!!! Thank you very much.👌👌🙏🙏

[@marksant5488]

Alberto, spot on! You have just helped me out so much, i feel like crying. 🙂 Thank you so much. 😍You, your videos and classes are brilliant. Wishing you the best from Prague.

[@sabastian61]

Been struggling with this. This has really helped me. Thank you!

[@abhijeetghosh27]

This is awesome. Saved my day 😀👍 Appreciate you creating these great learning content and sharing it with us. Thanks!

[@tashagrihma6052]

Thank you so much! This was my issue at work this week. And love the new beard!

[@rusttaf]

Great explanation of the problem and the logical reason behind it. Thank you!

[@leandrohugosobrino7741]

Very very nice examples! At minute 20.06 in the metric "Transactions minus 500 Wrong" you are slicing first by continent and then by country, but the metric remains the same with VALUES (Customer[Continent]). I think you were expecting the total to change when slicing by country but it didn't because it should have change the metric to VALUES (Customer[Country])

[@user-cv1nk9ir5f]

Thanks very much for this. Tremendously helpful a dashboard I'm working on. You have a new subscriber sir!

[@hnp250]

This is PURE GOLD!! Thank you SQLBI

[@ThuyTruong-wo6fr]

Thank you so much! Very easy to understand!!!!

[@yangfantan2247]

excellent and elegant explanation!!

[@pedja2518]

Very nice explained.Thank you!

[@datasapien]

Bearded Alberto is the best Alberto yet.

[@successfullife7689]

Helped alot ! Thank you very much.

[@mohanravi3532]

Such a great explanation Sir! I've understood dax functions more clearly. Thanks a lot!! But sir, what if I add more dimensions, for an example if I add continent and country, then how should I correct it in dax? Can I add two column names in VALUES() funation?

[@ozansen_ozibaba]

VALUES() doesn't accept 2 column names by syntax. You will need summarize() function then!

[@michaeldes9566]

Thank you, these instructions are very helpful. Kind of a shame that browsing Microsoft's forums are a total mess if you want to understand how this works (for something that should be a simple task).

[@zerayacobhabemariam2726]

Very good video Can you make a video on transaction or number of transaction frequencies

[@sau_NK626]

thanks your video! If i want to use column with year/quater/month,and three months will have a total column to compare the data Is that possible?

[@Negi_From_Pauri]

Hey pal i am big fan of yours, "POWER BI"☺ i have a question about power query we can undo(means delete) the step, So is there any way we can redu(means to get last deleted step) the step. Deleted step could be in any line in applied steps. I am asking because sometimes the step is big we write m query so that we just can redu the step instead of to write m query again.

[@sbn4862]

it's 8's month that I always wondered why it's so. and not always can correct. Thank you. It's 3th time I see it today ;)

[@nishantkumar9570]

Can we make the field inside VALUES( ) dynamic using parameter? Like the columns used as dimension for the summarization can be fed into VALUES( ).

[@beginho2454]

Thanks Alberto all the time 👏🏻👏🏻

[@HoaNguyen-ji5fz]

Great video. Thank you

[@alvarorodriguezlasso]

Guao, fantastic, regards from Cali-Colombia

[@junshi9897]

Can I ask a question? I am new to the powerBi. I am trying to understand at around 12:50, why we have to add Calculate over AverageX? Is it because of the filter context? Thanks.

[@Urgosandspirit]

Thanks a lot! Did I understand right that basically forcing an additive calculation cannot happen for all of the tables / filter context I have in the data model? I will need to choose one or each of them depending on needs of the report?

[@SQLBI]

The behavior of a measure is defined by the measure itself, not by the report. Visual calculations may change that in the future, but the measures in the model works at the model level.

[@reshmaarapura6512]

Thank you for the excellent explanation.

[@luisfernandomacedo1451]

As always, another great explanation on one the of most intriguing topics. I am newbie to BI, and your videos has helped me a lot.

[@ukaszbalcerzak3191]

Hello SQLBI, thanks you for another amazing video. While working with DAX I'm often faced with the challenge to discover wether a calculation is additive in general (like sum of sales) or maybe it is not additive and over some dimensions. Is there a universal way of checking wether a formula is additive or not ? Usually I just try to understand the calculation and test it. All best

[@SQLBI]

Not directly, it should be documented by the model author or implicit in the business meaning of the measure. Otherwise, you have to analyze the code.

[@thepakcolapcar]

Very nice explanation. I am working on similar situation where it involves absolute vales (i.e. using ABS function). And I am facing similar mismatch of totals between sum of rows and the totals row. Can I say ABS is also a non-additive and therefore the mismatches in totals is expected?

[@SQLBI]

If you sum original values, it's easy to consider it additive as long as you aggregate two SUM by filtering positive and negative numbers in each one. If you sum semi-aggregate values and you must consider the ABS, that's a different story.

[@galymzhankenesbekov7242]

Thank you for an amazing video, could you please show the modified version of Average ( a.k.a weighted average). I have a problem that I have two columns the first is average and the second are the values which will be used as weights.

[@SQLBI]

You might consider something like AVERAGEX ( table, table[a] * table[w] )

[@MDevion]

Honestly it has always been this way, see MD cubes and MDX. Now I understand from a newbie perspective, but there are some "seniors" who make a fuss about this, who simply don't understand simple programming logic. DistinctCount is a perfect example. What Dax IS missing is lastchild, first child etc.

[@vasilisa6128]

Thank you! It solved my problem.

[@visheshjjain]

What would be the behaviour of the totals, when variables are used in such scenarios? Apart from teaching us what to do, thank you for teaching us what not to do as well! 🙏

[@SQLBI]

Variables are constants used in part of the evaluation - they do not affect this behavior. Thanks!

[@sr5726]

Why can't Microsoft just have a formatting to optionally turn off totals for measures , also providing a fx button to write how totals need to be calculated when switched on. Writing dax just to get rid of totals is a pain. Rather than focussing on logic, it makes one write more dax code on top of core logic , just to make total look better. Thank you so much for the video

[@SQLBI]

You can already disable totals and subtotals in the matrix visual in Power BI. However, it's a visual feature, not a model one.

[@CleitondaSilvaLeal]

Thats Amazing, thanks for sharing this.

[@ianmaddrell2247]

Great explanation

[@robertkiermasz4513]

vary good tutorial.

[@edgards]

Thanks Maestro!

[@SM23Studio]

thanks, it's clear explanation

[@konstantinvolke4569]

Thank you Alberto for this video. This is a fairly simple scenario with no hierarchy on the rows. I understand that additivity can be achieved by iterating over the granularity of the table. For instance if I want to see the #transactions-500 by product color and continent I would need to create an iterator e.g. by using SUMMARIZE(Sales, [continent], [product_color]), correct?

[@SQLBI]

Correct!

[@mohanravi3532]

Hi, Can you please elaborate how to form DAX for the above said scenario using Summarize function?

[@kavisimbu7383]

In this , How to apply additive forcefully whenever the user selects any field from any table.

[@haroldm.7325]

Excellent video ¡

[@Sandeep-lx5dz]

How to dynamically force additivity for the last example (sales amount gt $1M) based on selected dimension from Field Parameters (of multiple columns/dimensions)?

[@SQLBI]

That's not possible, you cannot easily adapt the DAX code to the field parameters selection. You could try by using ISINSCOPE on specific columns, but the code would be long and complex to maintain, plus possible performance issues.

[@Sandeep-lx5dz]

20:41 That's totally insane!

[@mategombas771]

Nice work Santa!

[@juanpablorvvv]

So how do we get rid of totals in no additive measures? How do we force a blank value in the total?

[@SQLBI]

In DAX you can use ISINSCOPE (better) or HASONEVALUE (slower) to identify whether you are not at the desired granularity. In Power BI, you can disable the totals for each visual.

[@andysuzhou7956]

Well explained!!!

[@sherifffruitfly]

If Power BI added up my Distinct Customer Count measure in the Total row, I would stop using Power BI.

[@SQLBI]

:)

[@rahulverma-pg1bk]

Really nice video

[@osielgutierrez7837]

Greats, regards

[@saqibsa100]

You are a champion!

[@yordanosadigo]

YES .... Thank you !

[@Aquaelus]

This was a Insane Eyeopener 😳

[@datamandy8975]

Well Explained!!!!

[@JMcLeodKC711]

Gotta love this stuff….distinct count baby

[@dariuszspiewak5624]

First time I can see Alberto with a beard :) Alberto, you look quite alright! :)

[@GreatNishan]

Thank you boss

[@mauriciovillatoro9361]

Shouldn't it be easier to just sum the visible amounts in the visual?

[@nishantkumar9570]

New look!

[@user-qg3vf8gy3u]

Why did you leave the product count, the first calculation incorrect? Since you showed how to make count additive, shouldn't you show how to obtain a correct subtotal as well?

Este é o mestre dos magos

[@rasmusl8370]

Sooo waiting for your comment, Greg Deckler... 😅

[@atanasprodatanasov9233]

Yes... I can't understand all the complaints about that. It is the way DAX works. And still can't understand why anyone would like to force additivity to non- additive measures... He already made a video where he says he does not agree with Alberto's explanation...

[@pabeader1941]

The # of Products column is misnamed. # of Products implies ALL products. It should be named # of Distinct Products. Then there would be less chance of confusion. # of Products IS additive. Since DAX doesn't know what you named the column, you need to make sure you name it correctly. Maybe it should be named Total either? Should be named Unfiltered instead since it's the DAX measure without the row filters.

[@SQLBI]

It depends. In many businesses, # of Products is exactly what you meant with # of Distinct Products. Too long names might be disruptive in the report, better to rely on meaningful descriptions in those cases.

[@fernandonavarrete1541]

PLIS TRANSLATE TO SPANISH ORIGINAL , the IA KZbin is very poor ....tks

[@user-qg3vf8gy3u]

It's not my dax that is wrong. The auto row subtotal is wrong, lol, & we cannot change that

[@user-fu4iw3mi5w]

Whao Are You?😱

[@alexrubin5955]

This is BS. How are other reporting tools able to do this? No disrespect to anyone.

[@SQLBI]

Please, can you clarify what you do you mean with that?

[@alexrubin5955]

@@SQLBI Sorry, I was not referring to your explanation of DAX. It's just that, in 2023, it's a darn shame that Microsoft's tool does not have a feature that takes care of this problem out of the box.

[@kishoremohanraj7200]

In my case the measures total is showing Zero, I tried some ways to make it right but it is not working🥲

[@powerbinareal]

The monster"