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!.
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.
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 :)
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.
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
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!
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!
🎯 Key points for quick navigation: 00:00:00 *⁉️ Introduction to Total Calculation Confusion* - Introduction to the common question about perceived inaccuracies in Power BI total calculations. - Explanation that Power BI computes totals correctly, but the confusion arises from the type of calculation: additive vs. non-additive. 00:02:28 *➕ Additive Calculations and Sum X* - Description and example of additive calculations using a sales amount, where individual row sums equal the total. - Introduction of "Sum X" for calculations and ensuring correctness across different attributes. 00:04:18 *🔄 Understanding Non-Additive Calculations* - Explanation of non-additive calculations using a distinct count of products as an example. - Discussion on the challenges and misinterpretations that occur due to non-additive calculations like the distinct count. 00:05:07 *🧩 Forcing Additivity in Measures* - Demonstrating the method to force additivity on measures, using Sum X with iteration over desired tables or columns. - Observation of potential inaccuracies resulting from forced additivity, particularly issues of double counting. 00:07:59 *⚖️ Challenges with Averaging Averages* - Issues that arise from averaging numbers that are themselves averages, leading to data misinterpretation. - Presentation of a detailed example to illustrate inaccuracies generated when averaging previously averaged values. 00:15:31 *⚗️ Mixing Constants with Calculated Values* - Explanation of loss of additivity when combining constant values with context-dependent calculations. - Further illustrations of the concept using a simple measure involving transactions minus a constant value. 00:21:02 *🧮 Handling Complex Non-Additive Scenarios* - Example of intricacies resulting from a calculation meant to include sales only for countries exceeding a certain amount in sales. - Clarification on rewriting measures to include context iteration to properly reflect intended outcomes. 00:25:47 *🔍 Conclusion: The Importance of Understanding Additivity* - Summary of the importance of distinguishing between additive and non-additive calculations. - Encouragement for DAX developers to assess and adjust calculations for correct total computation by understanding data context and intent. Made with HARPA AI
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....
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.
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])
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
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?
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.
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).
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.
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?
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.
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.
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.
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! 🙏
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?
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?
What if I have 3 tables: Actual, Budget and Date. My Actuals and Budget table have sub categories for example Region. I created a matrix comparing Actuals vs Budget for selected month by Region. My Actual figure is from the Actuals table and my Budget figure is from my Budgets table. The Actuals by Region is summed up correctly but my Budgeted total by Region brings back the total for the year not for that month. For context my Date table has relationships to both my Actuals and Budget table.
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.
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.
How to dynamically force additivity for the last example (sales amount gt $1M) based on selected dimension from Field Parameters (of multiple columns/dimensions)?
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.
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
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...
I really hate totals in PowerBi and wish it was more like an excel spreadsheet that adds up all the records on the screen. This is certainly something that shouldn’t be such a pain in the butt.
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.
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.
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?
@@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.
![เจ็บที่ยังฮัก - ม่อน วรวิทย์ [ Official Mv ] จอนนี่มิวสิค](http://i.ytimg.com/vi/KALIGSRSjFs/mqdefault.jpg)
"...and that is totally insane." LOL, you did it again, Alberto, such a good teacher. This video is an eyeopener for all DAX newbies.
at 20:19 lol.
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!.
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.
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 :)
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.
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
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!
SUMX(
VALUES(Table[Column])
,Expression/Calculation/Measure
)
worked for me! THANKS!!!
This was pain in my bum for weeks!
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!
🎯 Key points for quick navigation:
00:00:00 *⁉️ Introduction to Total Calculation Confusion*
- Introduction to the common question about perceived inaccuracies in Power BI total calculations.
- Explanation that Power BI computes totals correctly, but the confusion arises from the type of calculation: additive vs. non-additive.
00:02:28 *➕ Additive Calculations and Sum X*
- Description and example of additive calculations using a sales amount, where individual row sums equal the total.
- Introduction of "Sum X" for calculations and ensuring correctness across different attributes.
00:04:18 *🔄 Understanding Non-Additive Calculations*
- Explanation of non-additive calculations using a distinct count of products as an example.
- Discussion on the challenges and misinterpretations that occur due to non-additive calculations like the distinct count.
00:05:07 *🧩 Forcing Additivity in Measures*
- Demonstrating the method to force additivity on measures, using Sum X with iteration over desired tables or columns.
- Observation of potential inaccuracies resulting from forced additivity, particularly issues of double counting.
00:07:59 *⚖️ Challenges with Averaging Averages*
- Issues that arise from averaging numbers that are themselves averages, leading to data misinterpretation.
- Presentation of a detailed example to illustrate inaccuracies generated when averaging previously averaged values.
00:15:31 *⚗️ Mixing Constants with Calculated Values*
- Explanation of loss of additivity when combining constant values with context-dependent calculations.
- Further illustrations of the concept using a simple measure involving transactions minus a constant value.
00:21:02 *🧮 Handling Complex Non-Additive Scenarios*
- Example of intricacies resulting from a calculation meant to include sales only for countries exceeding a certain amount in sales.
- Clarification on rewriting measures to include context iteration to properly reflect intended outcomes.
00:25:47 *🔍 Conclusion: The Importance of Understanding Additivity*
- Summary of the importance of distinguishing between additive and non-additive calculations.
- Encouragement for DAX developers to assess and adjust calculations for correct total computation by understanding data context and intent.
Made with HARPA AI
AMAZING, i was hitting my head on the wall over the DISTINCTCOUNT problem for hous, now i understand the problem. Thanks!!
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....
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.
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])
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
You can already disable totals and subtotals in the matrix visual in Power BI. However, it's a visual feature, not a model one.
Love you for your videos!!!! The way you articulate complex situations in such a simple way is comendable!
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?
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.
Great explanation of the problem and the logical reason behind it. Thank you!
Excellent exhaustive video with impeccable clarity!!! Thank you very much.👌👌🙏🙏
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).
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.
Bearded Alberto is the best Alberto yet.
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?
Been struggling with this. This has really helped me. Thank you!
Thank you so much! This was my issue at work this week. And love the new beard!
omg thank you very much a small solution that resolves my headache for half a week
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?
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.
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.
This is PURE GOLD!!
Thank you SQLBI
This is awesome. Saved my day 😀👍 Appreciate you creating these great learning content and sharing it with us. Thanks!
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.
You might consider something like AVERAGEX ( table, table[a] * table[w] )
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! 🙏
Variables are constants used in part of the evaluation - they do not affect this behavior.
Thanks!
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?
Correct!
Hi, Can you please elaborate how to form DAX for the above said scenario using Summarize function?
excellent and elegant explanation!!
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?
VALUES() doesn't accept 2 column names by syntax. You will need summarize() function then!
What if I have 3 tables: Actual, Budget and Date. My Actuals and Budget table have sub categories for example Region. I created a matrix comparing Actuals vs Budget for selected month by Region. My Actual figure is from the Actuals table and my Budget figure is from my Budgets table. The Actuals by Region is summed up correctly but my Budgeted total by Region brings back the total for the year not for that month. For context my Date table has relationships to both my Actuals and Budget table.
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 ;)
Can we make the field inside VALUES( ) dynamic using parameter? Like the columns used as dimension for the summarization can be fed into VALUES( ).
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.
Thank you so much! Very easy to understand!!!!
20:41 That's totally insane!
Thanks very much for this. Tremendously helpful a dashboard I'm working on. You have a new subscriber sir!
Very good video
Can you make a video on transaction or number of transaction frequencies
So how do we get rid of totals in no additive measures? How do we force a blank value in the total?
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.
Very nice explained.Thank you!
Thanks Alberto all the time 👏🏻👏🏻
Thank you for the excellent explanation.
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.
How to dynamically force additivity for the last example (sales amount gt $1M) based on selected dimension from Field Parameters (of multiple columns/dimensions)?
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.
In this ,
How to apply additive forcefully whenever the user selects any field from any table.
Great explanation
Guao, fantastic, regards from Cali-Colombia
Thank you! It solved my problem.
Great video. Thank you
Helped alot ! Thank you very much.
vary good tutorial.
If Power BI added up my Distinct Customer Count measure in the Total row, I would stop using Power BI.
:)
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
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.
Thanks Maestro!
thanks, it's clear explanation
Gotta love this stuff….distinct count baby
This was a Insane Eyeopener 😳
Nice work Santa!
Thats Amazing, thanks for sharing this.
YES .... Thank you !
Sooo waiting for your comment, Greg Deckler... 😅
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...
I really hate totals in PowerBi and wish it was more like an excel spreadsheet that adds up all the records on the screen. This is certainly something that shouldn’t be such a pain in the butt.
First time I can see Alberto with a beard :) Alberto, you look quite alright! :)
Shouldn't it be easier to just sum the visible amounts in the visual?
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.
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.
Well explained!!!
Excellent video ¡
You are a champion!
That was brilliant! 🤩
Well Explained!!!!
Really nice video
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?
Greats, regards
"And that is totally insane" 😂
PLIS TRANSLATE TO SPANISH ORIGINAL , the IA TH-cam is very poor ....tks
Thank you boss
New look!
It's not my dax that is wrong. The auto row subtotal is wrong, lol, & we cannot change that
Este é o mestre dos magos
This is BS. How are other reporting tools able to do this?
No disrespect to anyone.
Please, can you clarify what you do you mean with that?
@@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.
Whao Are You?😱
In my case the measures total is showing Zero, I tried some ways to make it right but it is not working🥲
The monster"