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

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.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors