Forum Discussion
Cumulative sum
Hello
I have a dataset where I'm trying to calculate the sum for each row (a cumulative sum). The dataset is ordered by a ranking that goes from 1-10. I have created a measure that works okay, but it's giving the same value for multiple rows when the ranking is the same. I understand why it happens (the filter finds more than one value and sums the table). How can I avoid this from happening?
Here is an example dataset:
| ID | Name | Amount | Rating | Cumulative sum |
| 1 | Ted | 1000 | 9 | 2000 |
| 2 | Morris | 1000 | 9 | 2000 |
| 3 | Harry | 1000 | 7 | 3000 |
| 4 | William | 1000 | 6 | 4000 |
| 5 | Sana | 1000 | 5 | 5000 |
Measure = CALCULATE(SUM(Sheet1[Amount]),
FILTER(ALLSELECTED(Sheet1),
Sheet1[Ranking] >= Min(Sheet1[Ranking])
)
)
How can I avoid this problem and still be able to cumulate values based on the descending column for rating?
3 Replies
HerrPowerBI Use your ID instead? If you need an absolute unique ranking, you can use this:
The Mythical DAX Index - Microsoft Power BI Community
Also, see if this helps:
Thank you for your response.
Unfortunately, none of the solutions above worked for me. I know how I can sort it by ID, but the rating is not following the order of ID's. I also have to be able to pick out someones name by using the filter and still be able to cumulate the values of that specific filtered view.
