When to use SUM and SUMX in DAX

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

ABOUT BI Gorilla:
BI Gorilla shares videos and articles on Power BI and Excel to help you improve your skills.

SUPPORT MY CHANNEL

SUBSCRIBE TO MY CHANNEL

LET'S CONNECT:

Thank you for your support!

#DAX #SUMX #BIGorilla

[joshuatoa]

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.

[Crusthide]

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.

[er]

Solid explanation. Clear as day. Thank you

[hejacolak]

Good explanation and easy to follow. Thanks!

[kirillperian]

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!

[rancohen]

Thank you very much. 
Amazing channel!

[monsieurashishk]

nice explanation. easy to understand. thank you

[tak]

Great explanation and easy to follow. Thanks!

[giri]

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

[Matheus_Oliveira_Xs]

It is true that the SUMX function may impact Power BI's performance?

[abhisheklondhe]

Please do share datasets taken in this example

[yanlizhang]

Thanks for the video. A question: why do you do VATsum? Why not just reference to VAT by country? Thanks

[antipghosh]

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

[statesman]

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.

[AT-LT]

Good Videos, but could please increase the video size of your examples. The data is hard to see that small.

[narendrababu]

Absolutely you cannot do 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.