When to use SUM and SUMX in DAX

[@joshuatoa8150]

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.

[@BIGorilla]

Thank you very much Joshua, this is the way I try to explain the concepts to students. It has helped me too!

[@lookingatdaisies9901]

Thanks for this video, I'm learning PowerBI on the Microsoft Learn website, and I noticed they're not using simple terms to describe processes but I could still work it out. I was finally stumped by the iterator functions, your video made it make sense. 🙏

[@anigrigoryan3386]

this video solved all the problems I had with my 1st powerBI dashboard! :D Thank you very much!

[@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!

[@BIGorilla]

Appreciate the compliment Kirill. More content coming soon!

[@hejacolak4648]

Good explanation and easy to follow. Thanks!

[@608er]

Solid explanation. Clear as day. Thank you

[@rancohen4353]

Thank you very much. Amazing channel!

[@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.

[@fr1sket363]

Nice and easy to follow, thanks.

[@BIGorilla]

Thanks for kind words. Are you using iterators yet? And how is that going for you fr1sket? Keep crushing it! Rick

[@tak0331]

Great explanation and easy to follow. Thanks!

[@BIGorilla]

Thank you!

[@monsieurashishk]

nice explanation. easy to understand. thank you

[@fnln8364]

Finally!!! Triple like!

[@mnowako]

Good one! Thank you!

[@BIGorilla]

Thanks!

[@unforgiven123321]

great explanation

[@Christian-it8bu]

THANKS BRO!

[@yanlizhang8057]

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

[@BIGorilla]

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?

[@LittlePotatoSaver]

Thank you!!

[@abhisheklondhe33449]

Please do share datasets taken in this example

[@statesman6379]

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.

[@aarthik2312]

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

[@statesman6379]

@@aarthik2312 Thanks for breaking it down. It makes sense now. Appreciate it!

[@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

[@BIGorilla]

Hi Antip, are you trying to do this in DAX or in M? Both are possibilities in Power BI

[@AT-victory]

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

[@BIGorilla]

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

[@AT-victory]

@@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.

[@BIGorilla]

Thanks! bigger videos coming up

[@Matheus_Oliveira_Xs1]

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

[@giri41]

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

[@narendrababu2287]

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.

[@BIGorilla]

Precisely. That's the point, continue the video to the right approach :) enjoy!

[@giri41]

If statement is ignored r😢😢