Forum Discussion

mfarina's avatar
mfarina
Frequent Visitor
9 years ago
Solved

Training Completion Percentage by Manager

I am trying to create a bar chart visualization that shows the percentage of staff who've completed training grouped by manager.  Two requirements are that a) only active staff should be considered, ...
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    mfarina

     

    According to your description, you want to calculated completion for each users group on manager. Right?

     

    In your scenario, since you want a staff member taking the same training more than once is counted the same as if they took it just once, you can firstly SUMMAZRIZECOLUMNS() on 'Completion' table to remove the duplicate rows. Then create a measure to calculate percentage for each user. After that we can SUMMARIZE() a new table for populating chart visual. Please see my sample:

     

     

     

    1. Remove duplicate rows in 'Completion' table with SUMMARIZECOLUMNS() function. Then create a "Completion Rate" measure:

     

    Completion Rate = CALCULATE(COUNTROWS(),ALLEXCEPT(Completion,Completion[User ID]),FILTER(Completion,Completion[Completed?]="True"))
/CALCULATE(COUNTROWS(),ALLEXCEPT(Completion,Completion[User ID]))

    2. Create a new calculated table with "InActive" Users filtered.

     

     

    User Completion = SUMMARIZE(FILTER(Completion,RELATED(User[User Status])="Active"),Completion[User ID],User[User Name],User[User Manager Name],"completion rate",Completion[Completion Rate])

     

     

    Regards,

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
9 years ago

mfarina

 

According to your description, you want to calculated completion for each users group on manager. Right?

 

In your scenario, since you want a staff member taking the same training more than once is counted the same as if they took it just once, you can firstly SUMMAZRIZECOLUMNS() on 'Completion' table to remove the duplicate rows. Then create a measure to calculate percentage for each user. After that we can SUMMARIZE() a new table for populating chart visual. Please see my sample:

 

 

 

1. Remove duplicate rows in 'Completion' table with SUMMARIZECOLUMNS() function. Then create a "Completion Rate" measure:

 

Completion Rate = CALCULATE(COUNTROWS(),ALLEXCEPT(Completion,Completion[User ID]),FILTER(Completion,Completion[Completed?]="True"))
/CALCULATE(COUNTROWS(),ALLEXCEPT(Completion,Completion[User ID]))

2. Create a new calculated table with "InActive" Users filtered.

 

 

User Completion = SUMMARIZE(FILTER(Completion,RELATED(User[User Status])="Active"),Completion[User ID],User[User Name],User[User Manager Name],"completion rate",Completion[Completion Rate])

 

 

Regards,