cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ncbshiva Member
Member

SUMX not giving correct totals

Hi Team,

 

Below is my sample data and i am using SUMX to calculate the correct total in my "Market Mix" measure , but i am not getting the correct total. Below total highlighted in green is the correct one. 

 

DAX formula for Market Mix Cal = (([Volume Variance]) * ([Market Average Price] - [AEM Average Price])).

for Market Mix = CALCULATE(

SUMX (
ADDCOLUMNS(SUMMARIZE(FactMeasures;'AEM Responsibility'[AEM Group];'AEM Responsibility'[AEM Responsibility]; Market[Market Name]); "MarketMix";[Market Mix Cal]);[MarketMix])).
 
Not sure why mu SUMX is ignoring some of the rows and giving me the incorrect totals. As you can see you can see in my below sample data the 4th row is ignored in my SUMX calculations.
 
Can anyone please help to get this totals correct ?

 

AEM GroupAEM ResponsibilityMarket NameVolume VarianceAEM Average PriceMarket Average PriceMarket Mix CalMarket Mix
Global Duty FreeDF Ferries & NWEShipping600098,82118,62118822118822
Global Duty FreeDF Ferries & NWEWholesale403298,82100,2356675667
Global Duty FreeDF Ferries & NWEFerries288598,8289,54-26787-26787
Global Duty FreeDF Ferries & NWEMilitary59798,8267,43-18726 
Global Duty FreeDF Ferries & NWEBorder Americas098,82 0 
Global Duty FreeDF Ferries & NWECalais098,82 0 
Global Duty FreeDF Ferries & NWEMixed Channel098,82 00
Global Duty FreeDF Ferries & NWENot assigned098,82 00
Total     7897697702

 

Regards

SHIVA

3 REPLIES 3
Super User
Super User

Re: SUMX not giving correct totals

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

ncbshiva Member
Member

Re: SUMX not giving correct totals

Hi @Greg_Deckler ,

 

Thanks for the reply. This will work when you have only one table.

But in my sample data the columns are from different table , so how can i mention 3 columns from different tables in HASONEVALUE dax.

 

Can you help with the DAX formula ?

 

Regards

SHIVA

Super User
Super User

Re: SUMX not giving correct totals

Would need sample data for the three tables in order to build and test it.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 58 members 1,144 guests
Please welcome our newest community members: