Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
RehanSheikh
New Member

Different Results When Querying Table with Different DAX Formulas

Greetings Power BI Community:

 

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

Revenue = SUM (Sales [Sales Amount])
 
and 
 
RevenueX = SUMX(SalesSales[Sales Amount])
 
I received same Total Sum as shown below:
RehanSheikh_0-1659629004836.png

However, when I used the following DAX formula:

 

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

 

The sum was different as show hereunder:

 

RehanSheikh_1-1659629109051.png

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

 

Yours truly, 

 

Rehan Sheikh 

 

 
2 REPLIES 2
hircinelol
Regular Visitor

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
Super User
Super User

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Users online (1,238)