SUMX not giving correct totals

[ncbshiva]

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 Group	AEM Responsibility	Market Name	Volume Variance	AEM Average Price	Market Average Price	Market Mix Cal	Market Mix
Global Duty Free	DF Ferries & NWE	Shipping	6000	98,82	118,62	118822	118822
Global Duty Free	DF Ferries & NWE	Wholesale	4032	98,82	100,23	5667	5667
Global Duty Free	DF Ferries & NWE	Ferries	2885	98,82	89,54	-26787	-26787
Global Duty Free	DF Ferries & NWE	Military	597	98,82	67,43	-18726
Global Duty Free	DF Ferries & NWE	Border Americas	0	98,82		0
Global Duty Free	DF Ferries & NWE	Calais	0	98,82		0
Global Duty Free	DF Ferries & NWE	Mixed Channel	0	98,82		0	0
Global Duty Free	DF Ferries & NWE	Not assigned	0	98,82		0	0
Total						78976	97702

Regards

SHIVA

[Katie-Farrand12]

Hi @Greg_Deckler 

I am having this exact issue and I have no idea what could be causing it. It is so frustrating!!

Please see below my table of results. It is YTD budget data and should add up to 2693.67. But for some strange reason, it is adding to 2767.74. I have aboslutely no idea where the extra 74.07 is coming from. I am stumped. 

Results Table:

01/01/2023 00:00	38.71
02/01/2023 00:00	38.71
03/01/2023 00:00	38.71
04/01/2023 00:00	38.71
05/01/2023 00:00	38.71
06/01/2023 00:00	38.71
07/01/2023 00:00	38.71
08/01/2023 00:00	38.71
09/01/2023 00:00	38.71
10/01/2023 00:00	38.71
11/01/2023 00:00	38.71
12/01/2023 00:00	38.71
13/01/2023 00:00	38.71
14/01/2023 00:00	38.71
15/01/2023 00:00	38.71
16/01/2023 00:00	38.71
17/01/2023 00:00	38.71
18/01/2023 00:00	38.71
19/01/2023 00:00	38.71
20/01/2023 00:00	38.71
21/01/2023 00:00	38.71
22/01/2023 00:00	38.71
23/01/2023 00:00	38.71
24/01/2023 00:00	38.71
25/01/2023 00:00	38.71
26/01/2023 00:00	38.71
27/01/2023 00:00	38.71
28/01/2023 00:00	38.71
29/01/2023 00:00	38.71
30/01/2023 00:00	38.71
31/01/2023 00:00	38.71
01/02/2023 00:00	42.86
02/02/2023 00:00	42.86
03/02/2023 00:00	42.86
04/02/2023 00:00	42.86
05/02/2023 00:00	42.86
06/02/2023 00:00	42.86
07/02/2023 00:00	42.86
08/02/2023 00:00	42.86
09/02/2023 00:00	42.86
10/02/2023 00:00	42.86
11/02/2023 00:00	42.86
12/02/2023 00:00	42.86
13/02/2023 00:00	42.86
14/02/2023 00:00	42.86
15/02/2023 00:00	42.86
16/02/2023 00:00	42.86
17/02/2023 00:00	42.86
18/02/2023 00:00	42.86
19/02/2023 00:00	42.86
20/02/2023 00:00	42.86
21/02/2023 00:00	42.86
22/02/2023 00:00	42.86
23/02/2023 00:00	42.86
24/02/2023 00:00	42.86
25/02/2023 00:00	42.86
26/02/2023 00:00	42.86
27/02/2023 00:00	42.86
28/02/2023 00:00	42.86
01/03/2023 00:00	41.94
02/03/2023 00:00	41.94
03/03/2023 00:00	41.94
04/03/2023 00:00	41.94
05/03/2023 00:00	41.94
06/03/2023 00:00	41.94
07/03/2023 00:00	41.94

Formulae used (I have to use a measure like this because i cannot add tables or columns into this model)

YZ Harvest Tons YTD BUDGET =

VAR CurrentYear = YEAR(TODAY())

VAR YearStart = DATE(CurrentYear,1,1)

VAR currentMonthdays = SWITCH(MONTH(MIN('Calendar'[Date])), 1, 31, 2, 28, 3, 31, 4, 30, 5, 31, 6, 30, 7, 31, 8, 31, 9, 30, 10, 31, 11, 30, 12, 31, BLANK())

VAR YearlyTarget =

SWITCH(MONTH(MIN('Calendar'[Date])), 1, 1200, 2, 1200, 3, 1300, 4, 1400, 5, 1400, 6, 1400, 7, 1400, 8, 1350, 9, 1350, 10, 1400, 11, 1400, 12, 1400, BLANK())

VAR YearToDateTarget = SUMX(FILTER('Calendar', 'Calendar'[Date] >= YearStart && 'Calendar'[Date] <= TODAY() && YEAR('Calendar'[Date]) = CurrentYear), YearlyTarget /(currentMonthdays))

RETURN YearToDateTarget

Thanks, Katie

[Greg_Deckler]

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

[ncbshiva]

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

[Greg_Deckler]

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