When to use SUM and SUMX in DAX

แชร์
ฝัง
  • เผยแพร่เมื่อ 24 ธ.ค. 2024

ความคิดเห็น • 38

  • @joshuatoa8150
    @joshuatoa8150 2 ปีที่แล้ว +5

    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
      @BIGorilla  2 ปีที่แล้ว +1

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

  • @anigrigoryan3386
    @anigrigoryan3386 หลายเดือนก่อน

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

  • @lookingatdaisies9901
    @lookingatdaisies9901 3 หลายเดือนก่อน

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

  • @hejacolak4648
    @hejacolak4648 10 หลายเดือนก่อน

    Good explanation and easy to follow. Thanks!

  • @608er
    @608er ปีที่แล้ว

    Solid explanation. Clear as day. Thank you

  • @rancohen4353
    @rancohen4353 ปีที่แล้ว

    Thank you very much.
    Amazing channel!

  • @fr1sket363
    @fr1sket363 3 ปีที่แล้ว +1

    Nice and easy to follow, thanks.

    • @BIGorilla
      @BIGorilla  3 ปีที่แล้ว

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

  • @fnln8364
    @fnln8364 ปีที่แล้ว

    Finally!!! Triple like!

  • @mnowako
    @mnowako 3 ปีที่แล้ว +1

    Good one! Thank you!

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

  • @unforgiven123321
    @unforgiven123321 4 หลายเดือนก่อน

    great explanation

  • @monsieurashishk
    @monsieurashishk 2 ปีที่แล้ว

    nice explanation. easy to understand. thank you

  • @kirillperian
    @kirillperian 3 ปีที่แล้ว +2

    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
      @BIGorilla  3 ปีที่แล้ว

      Appreciate the compliment Kirill. More content coming soon!

  • @tak0331
    @tak0331 3 ปีที่แล้ว

    Great explanation and easy to follow. Thanks!

  • @LittlePotatoSaver
    @LittlePotatoSaver ปีที่แล้ว

    Thank you!!

  • @Christian-it8bu
    @Christian-it8bu 4 หลายเดือนก่อน

    THANKS BRO!

  • @statesman6379
    @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
      @aarthik2312 6 หลายเดือนก่อน

      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
      @statesman6379 6 หลายเดือนก่อน

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

  • @yanlizhang8057
    @yanlizhang8057 3 ปีที่แล้ว

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

    • @BIGorilla
      @BIGorilla  3 ปีที่แล้ว +1

      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?

  • @abhisheklondhe33449
    @abhisheklondhe33449 2 ปีที่แล้ว

    Please do share datasets taken in this example

  • @Matheus_Oliveira_Xs1
    @Matheus_Oliveira_Xs1 9 หลายเดือนก่อน

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

  • @antipghosh
    @antipghosh 2 ปีที่แล้ว

    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
      @BIGorilla  2 ปีที่แล้ว

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

  • @AT-victory
    @AT-victory 2 ปีที่แล้ว

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

    • @BIGorilla
      @BIGorilla  2 ปีที่แล้ว +1

      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
      @AT-victory 2 ปีที่แล้ว +1

      @@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
      @BIGorilla  2 ปีที่แล้ว

      Thanks! bigger videos coming up

  • @giri41
    @giri41 9 หลายเดือนก่อน

    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
    @narendrababu2287 3 ปีที่แล้ว

    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
      @BIGorilla  3 ปีที่แล้ว

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

  • @giri41
    @giri41 9 หลายเดือนก่อน

    If statement is ignored r😢😢