SUMX vs SUM gives different total - why and best practice

[Anonymous]

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? 

[IK1]

 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 

Explanation on sum vs sumx 

[tmhalila]

Thanks for sharing the guy explain very well with examples..!!

[Phil_Seamark]

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/

[Anonymous]

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

[Phil_Seamark · Accepted 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????