Different Results When Querying Table with Different DAX Formulas

[RehanSheikh]

Greetings Power BI Community:

I have issue. Whenever, I used the followinng DAX formulas

Revenue = SUM (Sales [Sales Amount])

and 

RevenueX = SUMX(Sales, Sales[Sales Amount])

I received same Total Sum as shown below:

However, when I used the following DAX formula:

RevenueX1 = SUMX(Sales,Sales[Order Quantity] * Sales[Unit Price] )

The sum was different as show hereunder:

Kindly guide me about the difference for $527,507.89; in fact the sum must be same. 

Yours truly, 

Rehan Sheikh 

[hircinelol]

Hi @RehanSheikh ,

You may take a look at this document.

https://powerbidocs.com/2020/10/11/dax-sum-and-sumx-functions/ .

Best Regards,

Jay

[selimovd]

Hey @RehanSheikh ,

the functions SUM and SUMX work differently.

The function SUM is calculating the sum of all the values in the current filter context of a specific column.

The function SUMX is iterating. This means it's going row by row and calculating the expression that you give as second parameter. All of that happens also in the current filter context.

In your first example (RevenueX = SUMX(Sales, Sales[Sales Amount])) the SUMX function is going through every row and is then only taking the value of [Sales Amount]. At the end all values of [Sales Amount] will be summed up. That's the reason you have the same result, because you do exactly the same like in SUM. Actually a SUM function is executed as SUMX with only the column name. So because you do exactly the same, you get the same result.

With your third approach (RevenueX1 = SUMX(Sales,Sales[Order Quantity] * Sales[Unit Price] )) the SUMX formula is again iterating row by row and in every row it's calculating [Order Quantity] * [Unit Price]. When all the iterations are finished it's doing the sum of all the [Order Quantity] * [Unit Price] that you calculated on a row level.

Because you do something differently you will get a different result.

You can add a calculated column and calculate Sales[Order Quantity] * Sales[Unit Price]. This result will be different than the column [Sales Amount] and the difference between these two columns is your difference in the two approaches.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic