Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have Raw data [Fiscal Qtr ending] & [Net Revenue]. I am trying to get PBI to return a {Total Net Revenue] "x numbers of days back". SO I use DAX ... Calculate(SUM, DATESINBETWEEN)
I started off using Calculate(SUMX , DATESINBETWEEN) - and I notice very small discrepancy (Refer image RED)
However, if I use Calculate(SUM, DATESINBETWEEN) it is correct..
I dont know if SUMX is the wrong DAX to use - clearly this is the case..
Can you help me understand why?
Solved! Go to Solution.
I guess the difference is that with SUM, if you have 100 rows of data, it can do it in a single operation.
Whereas with SUMX there will be 100 separate calcuations which then result in a final amount which opens the door for rounding issues????
To understand this you need to understand the functionality of both sum and sumx .if both of them were to give you the same result then why have 2 separate functions at all?
Sumx is iterative in nature and acts row by row.It does the calculation on the table you specify in the argument.For ex : if you write the below syntax
Sumx(financials, Unit_Price*Quantity)
Then sumx will go to the table financials,then for the 1st row multiply Unit_Price*Quantity and simply go to the second row for the same calculation .after all rows are over it does a sum on top of it.
Sum function simply adds up numeric column values and gives you the result in the visual based on other columns you select.
Sum works on a single column while sumx can work on multiple columns.
Found a simple video with indepth explanation that might help you more
Thanks for sharing the guy explain very well with examples..!!
Hi @Anonymous,
That is a very good question and lucky for you there is a very good answer here in this blog post by @MattAllington
http://exceleratorbi.com.au/sum-vs-sumx-in-dax/
Whilst the conclusion here is "Agregate a single column, use SUM".. do we know why in my examples SUMX returned a tiny discrepancy?
Thank you Phil
I guess the difference is that with SUM, if you have 100 rows of data, it can do it in a single operation.
Whereas with SUMX there will be 100 separate calcuations which then result in a final amount which opens the door for rounding issues????
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |