Forum Discussion
Running Total on non ordered values
Hi,
I need a help with following scenario:
I need to present cumulative value by category and category must be ordered asc by value.
When calculating it using
Value Cumulative = calculate(sum(Sales[value]);filter(ALLSELECTED(Sales[category]); Sales[category]<=max(Sales[category])))
it works in alphabetical order, but when trying to sort the cumulation breaks
What is needed is 2nd table with Value Cumulative 5,9,12.
Basically the question is if it is there any way to calculate cumulatively when you dan't have ordered column reference like date or number. I know I could calculate ranking column and reference it in filter but it woudln't be flexible when changeing aggregation levels or filters.
Thanks for any help
hi,@raionpbi
After my research, you can use this formula as below:
Measure = CALCULATE(SUM(Sales[value]),FILTER(ALL(Sales),[rank]<=MAXX(Sales,[rank])))
Result:
here is pbix, please try it.
https://www.dropbox.com/s/y78z2mozfxof0hz/Running%20Total%20on%20non%20ordered%20values.pbix?dl=0
Best Regards,
Lin
2 Replies
- v-lili6-msftCommunity Support
hi,@raionpbi
After my research, you can use this formula as below:
Measure = CALCULATE(SUM(Sales[value]),FILTER(ALL(Sales),[rank]<=MAXX(Sales,[rank])))
Result:
here is pbix, please try it.
https://www.dropbox.com/s/y78z2mozfxof0hz/Running%20Total%20on%20non%20ordered%20values.pbix?dl=0
Best Regards,
Lin
- raionpbiFrequent Visitor
Thanks a lot!