When to use SUM and SUMX in DAX
HTML-код
- Опубликовано: 20 апр 2021
- This video compares SUM to the SUMX function in DAX. What are the similarities between SUM and SUMX, how are they different and I provide some measure examples. Understanding iterators will help you get the most out of Power BI.
Master Functions and Syntax in M
powerquery.how
ABOUT BI Gorilla:
BI Gorilla shares videos and articles on Power BI and Excel to help you improve your skills.
SUPPORT MY CHANNEL
Any videos are made free of charge. You can support my channel by giving a donation through: paypal.me/rickmaurinus.
Website: gorilla.bi
SUBSCRIBE TO MY CHANNEL
ruclips.net/user/bigorilla?sub_con...
LET'S CONNECT:
Blog: gorilla.bi
Facebook: / bigorilla
Twitter: / rickmaurinus
LinkedIn: / rickmaurinus
Thank you for your support!
#DAX #SUMX #BIGorilla
Best breakdown for SUMX I've seen yet. The reason why I say this is because I actually understand how this works and how to use it now.
Thank you very much Joshua, this is the way I try to explain the concepts to students. It has helped me too!
Solid explanation. Clear as day. Thank you
Thank you very much.
Amazing channel!
nice explanation. easy to understand. thank you
Thank you!!
Good explanation and easy to follow. Thanks!
Nice and easy to follow, thanks.
Thanks for kind words. Are you using iterators yet? And how is that going for you fr1sket?
Keep crushing it!
Rick
Great explanation and easy to follow. Thanks!
Thank you!
Good one! Thank you!
Thanks!
thanks, that was very helpful! The problem I'm having right now is that simpler measures ( something like CALCULATE(AVERAGE('Pricelist'[Price]),ALL('Pricelist')) - yes, tried ALL() and REMOVEFILTERS() to no avail) are no longer in static context once I start using more complex iterating measures in related table and doing slicers on my visuals.
Finally!!! Triple like!
Thanks, Rick! One of the best explanations of context transition concept that I have heard so far! I also like how you gradually increase the difficulty. This is frequently quite the barrier for junior DAX developers to overcome. Cheers!
Appreciate the compliment Kirill. More content coming soon!
Please do share datasets taken in this example
Thanks for the video. A question: why do you do VATsum? Why not just reference to VAT by country? Thanks
Hi Yanli. At 11:59 mins, the reference is to the VAT column. It's there that I use the SUMX iterator. Earlier in the video, I reference the VAT sum because in the matrix visual, by default there is no row context. And up to that point, I didn't use an iterator that creates that row context. Without a row context, to return a result, I had to use an aggregation function like SUM. Does that help?
Sir, How to do summation of first 3 numbers in a series in power bi....?
Series: {5, 7, 2,3,9,10,2}
Desired sum: 5+7+2
Hi Antip, are you trying to do this in DAX or in M? Both are possibilities in Power BI
It is true that the SUMX function may impact Power BI's performance?
Good Videos, but could please increase the video size of your examples. The data is hard to see that small.
Hey Allen, appreciate the feedback. I will make sure the new videos show bigger examples. I'm guessing you watch on Mobile?
Thanks again!
Rick
@@BIGorilla yes sometimes, but even on my desktop, there are a few times I am not able to expand the screen to see what you had just typed in. You generally do enlarge the screen afterwards, but I have missed some details in the past that you are working on. I do appreciate how you present your subject matter and will continue to look forward to your videos.
Thanks! bigger videos coming up
great video, but please help this logic not working here
Test_1 =
var PriceperUnit_CY = DIVIDE([Sales_CY],[Quantity_CY],0)
var PriceperUnit_PY = DIVIDE([Sales_PY],[Quantity_PY],0)
var SalesVariance = [Sales_CY]-[Sales_PY]
return
SUMX(VALUES(ATC[material_style1]),if(DIVIDE([Sales_CY],[Quantity_CY],0)=0 || DIVIDE([Sales_PY],[Quantity_PY],0)=0,[Sales_CY]-[Sales_PY],1))
At 5:05 you didn't explain why we are getting the absurd numbers. You only said that we get absurd results. I'm trying to figure out where the numbers are coming from. I'm having similar results of absurd numbers when I drop my formula into a calculated column, but when I use a measure, the numbers are correct. I would like to know what exactly is happening.
My calculated column formula is simply =SUMX(Sales, fSales[LineSales]) Why when I drop it into a Pivot Table, there are exaggerated numbers per row, but when I create the same formula via a measure, and drop it into a Pivot Table I get the correct results? I'm not sure if my absurd results and yours @5:05 are due to the same issue.
Those huge numbers are because the multiplication happens after summing up all of the unit column and all the price column.
What is actually correct is - multiply row by row for total price and then sum up the total price values.
For example:
unit | Price
10 5
20 10
correct measure: multiply row by row for total price and then sum up the total price - (10*5) + (20*10) = 50+200 = 250
wrong measure: sums up all the unit, sums up all the price and then multiplies the sum of each - (10+20) * (5+10) = 30*15 = 450
@@aarthik2312 Thanks for breaking it down. It makes sense now. Appreciate it!
If statement is ignored r😢😢
Absolutely you cannot do sum(sales(price))*sum(sales(units)) whats happening is
1) you are calculating sum(sales(price)) for belgium
2) you are calculating sum(sales(units))
For belgium
3) result will be a big and wrong result.
Sum of sales should be calculated individually for each product and then will be added for a region wise which results in a correct answer.
Because each product price will be different and number of units will be different, so that will never give you the correct result. So you need to do it row by row only, you can't do sum individually for Price and units and then multiply it.
We just need to have a theoretical idea how sales will be calculated otherwise we will perform wrong calculations.
Precisely. That's the point, continue the video to the right approach :) enjoy!