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.
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(
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
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)
Thanks, Katie
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
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
Would need sample data for the three tables in order to build and test it.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |