Forum Discussion
How to create custom slicer for multiple column
Hi Everyone, I'm struggling to do the custom slicer. I hope you guys can help me. Here is the question, I have a lists of staffs with the lists of trainings courses and expiry status. What I wan...
Hi Stachu,
Yes. In the interim period I'm doing it unpivot as well.
However, I have to upload 2 online excel into the desktop.
1. online excel for the % completion purpose.
2. another online excel for custom slicer purpose.
As for the % completion, I'm using the count completion to create the graph (last column from right) on order to create the graph as picture below.
if you calculate the completion rates in PowerBI rather than in Excel, then I think it would work fine
consider table like this
| Staff ID | Name | Training | Validity | Status |
| 1234567 | AAAA | Training A | Not Required | Not Required |
| 1234567 | AAAA | Training B | Not Required | Not Required |
| 1234567 | AAAA | Training C | Not Required | Not Required |
| 1234567 | AAAA | Training D | Not Required | Not Required |
| 1234567 | AAAA | Training E | Not Required | Not Required |
| 1234567 | AAAA | Training F | Valid | 1-2 months |
| 8901234 | BBBB | Training A | Valid | >3 months |
| 8901234 | BBBB | Training B | Valid | >3 months |
| 8901234 | BBBB | Training C | Valid | >3 months |
| 8901234 | BBBB | Training D | Valid | >3 months |
| 8901234 | BBBB | Training E | Valid | 1-2 months |
| 8901234 | BBBB | Training F | Valid | <1 month |
| 5678901 | CCCC | Training A | Valid | <1 month |
| 5678901 | CCCC | Training B | In Valid | Expired |
| 5678901 | CCCC | Training C | Not Required | Not Required |
| 5678901 | CCCC | Training D | Not Required | Not Required |
| 5678901 | CCCC | Training E | In Valid | Expired |
| 5678901 | CCCC | Training F | Valid | >3 months |
| 2345678 | DDD | Training A | Valid | >3 months |
| 2345678 | DDD | Training B | Valid | 1-2 months |
| 2345678 | DDD | Training C | Valid | 1-2 months |
| 2345678 | DDD | Training D | Valid | 1-2 months |
| 2345678 | DDD | Training E | In Valid | Expired |
| 2345678 | DDD | Training F | Valid | <1 month |
and measures like this
Completion = CALCULATE(COUNTROWS('Table1'),'Table1'[Validity]="Valid")
Total Training Courses = CALCULATE(COUNTROWS('Table1'),'Table1'[Validity]<>"Not Required")
% Completion = DIVIDE([Completion],[Total Training Courses])
they do give the same results as Excel in the employee filter context, you may just need to play a bit more with the filters to get the right totals (I'm not sure if 14/17 is consistent with your requirements for this four)