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,
I'm looking for a DAX formula (or maybe change in the data model?) for my cumulative measure. Look at the example, please:
Two tables, A and B, both with two columns - month and Cost (aggregated in Sum A and Sum B measures). Both tables linked to the 'months' table.
Values in particular months are shown in the first two columns.
I want to create a measure, which, starting from the current month, will take greater of the Sum A and Sum B values (and always Sum A for past months).
I tried to do the following:
Unfortunately, because in Power BI Totals (e.g. in a table visual) are calculated not as sum of rows, but for the current (total) context, it doesn't work correctly. See the following data:
The 'Future Sum' part (1) in 'Total Sum' is taken as 400 (greater of totals of Future Sum A / B) -with 300 from Historical should be 800 and is 700.
And, for the cumulative measure, I'd like to see 800 in the 2020-05 row (2). And here I have no idea where comes here 200 from (diff between 700 and 500).
Do you have any idea how to get such a cumulative measure?
TIA,
Ryszard.
Solved! Go to Solution.
Hi @rylach
Try these:
Future Sum V2 =
SUMX (
DISTINCT ( months[month] );
IF ( [Future Sum A] > [Future Sum B]; [Future Sum A]; [Future Sum B] )
)
Total Sum V2 = SUMX(DISTINCT(months[month]); [Historical sum] + [Future Sum])
Cumulative Total Sum V2 =
SUMX (
CALCULATETABLE (
DISTINCT ( months[month] );
FILTER ( ALLSELECTED ( months ); months[month] <= MAX ( months[month] ) )
);
[Total Sum V2]
)
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Hi @rylach
Can you perhaps share the pbix (or a mock version) that reproduces the problem?
It'll be easier to help
Best
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Hi AIB,
Yes, that's why I prepared the sample report.
See the report in the shared folder: https://u.unity.pl/3avd
R.
Hi @rylach
Try these:
Future Sum V2 =
SUMX (
DISTINCT ( months[month] );
IF ( [Future Sum A] > [Future Sum B]; [Future Sum A]; [Future Sum B] )
)
Total Sum V2 = SUMX(DISTINCT(months[month]); [Historical sum] + [Future Sum])
Cumulative Total Sum V2 =
SUMX (
CALCULATETABLE (
DISTINCT ( months[month] );
FILTER ( ALLSELECTED ( months ); months[month] <= MAX ( months[month] ) )
);
[Total Sum V2]
)
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
It works, thank you very much!
In fact, only change of the Future Sum formula fixed all results.
You proposed to change CALCULATE to iterator (in Cumulative measure) as a general good practice, or there are possible cases where the old formula would not work?
Ryszard
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 |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |