Forum Discussion
HELP - Cumulative count for latest value (string)
Hi Everyone,
I am struggling with a issue even if I feel like it is not that complicated. I need to get the cumulative count of latest grade for employees.
January : If employee A gets Grade A and employee B and employee C get Grade B -> Total A : 1 and Total B : 2
February : if Employee B gets Grade A -> Total A : 2 and Total B : 1
Here is a more detail exemple :
| Employee | Date | Month | Grade |
| E1 | 01/01/2021 | 1 | A |
| E2 | 04/01/2021 | 1 | B |
| E3 | 03/02/2021 | 2 | A |
| E4 | 12/02/2021 | 2 | B |
| E5 | 18/02/2021 | 2 | C |
| E5 | 03/03/2021 | 3 | A |
| E7 | 03/03/2021 | 3 | A |
| E8 | 19/03/2021 | 3 | A |
| E9 | 01/04/2021 | 4 | A |
| E4 | 03/04/2021 | 4 | A |
Results expected :
| Month | TOTAL A | TOTAL B | TOTAL C |
| 1 | 1 | ||
| 1 | 1 | 1 | |
| 2 | 2 | 1 | |
| 2 | 2 | 2 | |
| 2 | 2 | 2 | 1 |
| 3 | 3 | 2 | |
| 3 | 4 | 2 | |
| 3 | 5 | 2 | |
| 4 | 6 | 2 | |
| 4 | 7 | 1 |
Thanks in advance for helping me with this case!
Please try to create three similar measures.
Total A = CALCULATE(COUNTX(FILTER(ALLSELECTED('Table'),'Table'[Month]<=MAX('Table'[Month])&&'Table'[Grade]="A"),'Table'[Employee]))
4 Replies
xichiza , Add index column to the table.
Add month and index column on row of matrix
Grade on column of matrix
and this measure on value
countx(allseleceted(Table), Table[Month]<=Table[Month] && Table[Grade] = Max(Table[Grade]) , Table[employee])
Hi amitchandak , thanks for answering.
I have tried the formula but I am getting an error. Please see attached the pbix I used and formula error.
Also to clarify what I need at the end :
thanks for your help
amitchandak any update on your end ? Don't know if it is understable .. Let me know ! thanks
Please try to create three similar measures.
Total A = CALCULATE(COUNTX(FILTER(ALLSELECTED('Table'),'Table'[Month]<=MAX('Table'[Month])&&'Table'[Grade]="A"),'Table'[Employee]))
