Forum Discussion
Cumulative calculation based on a Category
Hello,
I am completely lost as to how to achieve a cumulative calculation in Power Bi. I am assuming i will need a measure and not a calculated column?
I need to create two measures when a category is filtered. The table1 below is a raw table and when filtered for CAT = "A" then the measures shoud result in cumulative values as table 2 so I can see on a line plot.
Thanks in advance.
Table 1
| Date | CAT | Rate | Hours |
| 11-08-21 | A | 0.517 | 3.87 |
| 06-08-21 | A | 0.558 | 3.58 |
| 01-08-21 | C | 0.451 | 4.43 |
| 30-07-21 | D | 0.574 | 3.48 |
| 23-07-21 | C | 0.536 | 3.73 |
| 22-07-21 | E | 0.441 | 4.53 |
| 10-07-21 | F | 0.41 | 4.88 |
| 03-07-21 | E | 0.8 | 2.5 |
| 01-07-21 | C | 0.467 | 4.28 |
| 26-06-21 | B | 0.55 | 3.63 |
| 16-06-21 | A | 0.674 | 2.97 |
| 13-06-21 | G | 0.432 | 4.63 |
| 08-06-21 | F | 0.44 | 4.55 |
| 07-06-21 | G | 0.723 | 2.77 |
| 03-06-21 | H | 0.41 | 4.88 |
| 27-05-21 | H | 0.574 | 3.48 |
| 26-05-21 | J | 0.571 | 3.5 |
| 24-05-21 | A | 0.321 | 6.23 |
| 19-05-21 | B | 0.769 | 2.6 |
| 18-05-21 | D | 0.553 | 3.62 |
| 17-05-21 | C | 0.443 | 4.52 |
Table 2
| Date | CAT | Rate | Hours | Measure1 = Cumulative Rate | Measure 2 = Cumulative Hours |
| 11-08-21 | A | 0.517 | 3.87 | 0.517 | 3.87 |
| 06-08-21 | A | 0.558 | 3.58 | 1.075 | 7.45 |
| 16-06-21 | A | 0.674 | 2.97 | 1.749 | 10.42 |
| 24-05-21 | A | 0.321 | 6.23 | 2.07 | 16.65 |
v2 , Create measures like
calculate(sum(Table[Hour]), filter(allselected(Table), Table[category] = max( Table[category]) && Table[date] <= max( Table[date])))
calculate(sum(Table[Rate]), filter(allselected(Table), Table[category] = max( Table[category]) && Table[date] <= max( Table[date])))
2 Replies
- amitchandak
Super User
v2 , Create measures like
calculate(sum(Table[Hour]), filter(allselected(Table), Table[category] = max( Table[category]) && Table[date] <= max( Table[date])))
calculate(sum(Table[Rate]), filter(allselected(Table), Table[category] = max( Table[category]) && Table[date] <= max( Table[date])))
Thanks amitchandak.
