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 

[v-cazheng-msft]

Hi @RehanSheikh,

When you do calculation in the measures such as MAX, Power BI will perform the same calculation for the total as well. Therefore, you may see the measure total isn’t what you want. When you encounter such kind of situation, you need modify the result of measure total by using some functions like IF, HASONEVALUE, HASONEFILTER etc..

Here are the blogs that you can refer. You can modify your dax by following them.

Measure Totals, The Final Word - Microsoft Power BI Community

Dealing with Measure Totals - Microsoft Power BI Community

If you still have problems on modifying your dax and need assistance, please post your problem in DAX&Commands Tips where you could get promptly support on dax. Thanks for your understanding!

https://community.powerbi.com/t5/DAX-Commands-and-Tips/bd-p/DAXCommands

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1448253#M6073...

Best Regards,                                                        

Community Support Team _ Caiyun