Forum Discussion
Cumulative Total applying to different visuals
Hi,
I'm using the following to calculate a cumulative total based on different stages of a process... i.e. stage 1 contains sum of 1,2,3 and 4... 2 contains sum of 2,3&4 etc etc...
The measure below works when using in a Funnel.
Measure = CALCULATE(SUM([MOQ Saving]),FILTER(ALL('MOQ Data'),'MOQ Data'[Stage]>=MIN('MOQ Data'[Stage])))
However when I use the same measure field in a table to show Supplier savings all of the supplier totals equal the grand total.... i have tried playing around with SUMX but to no avail and it's been a few years now since I last used DAX...
Please could somebody help?
Many thanks.
Hi dixon1983
This MEASURE should do it
Cumulative = CALCULATE ( SUM ( Table1[MOQ Saving] ), FILTER ( ALL ( Table1 ), Table1[Stage] >= SELECTEDVALUE ( Table1[Stage] ) ) )
3 Replies
v-chuncz-msftCommunity Support
Share us a complete example so that we could have a test. You may first take a look at ALLEXCEPT Function.
Hi,
Thanks for getting back to me.
Here is an example of the data :
Part Supplier MOQ Saving Stage 1 a 25 1 2 b 23 2 3 c 19 3 4 d 19 4 5 e 18 1 6 f 16 2 7 g 15 1 8 h 14 1 9 i 12 1 with required result being :
Stage Cumulative Saving 1 161 2 77 3 38 4 19 I would like to be able to represent the result using the Funnel visualization then also have a bar chart by supplier/MOQ saving which when I click on stage 1 in the funnel would show just those suppliers at stage 1 but with the correct MOQ saving next to each supplier (the issue I run into is that the total shows the grand total rather than the supplier total).
Hope that is enough detail but please let me know if not.
Many thanks for your help.
- Zubair_Muhammad
Community Champion
Hi dixon1983
This MEASURE should do it
Cumulative = CALCULATE ( SUM ( Table1[MOQ Saving] ), FILTER ( ALL ( Table1 ), Table1[Stage] >= SELECTEDVALUE ( Table1[Stage] ) ) )
