Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
Please try to create three similar measures.
Total A = CALCULATE(COUNTX(FILTER(ALLSELECTED('Table'),'Table'[Month]<=MAX('Table'[Month])&&'Table'[Grade]="A"),'Table'[Employee]))
@amitchandak any update on your end ? Don't know if it is understable .. Let me know ! thanks
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |