Training Completion Percentage by Manager

[mfarina]

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, and b) a staff member taking the same training more than once is counted the same as if they took it just once.  Training Title will be selectable via filter, and I'm assuming I can add a filter to the visualization where User Status is Active, but I'm still missing something.

I want the visualization to look like this, using the two tables below it as a highly simplified example of my data (note that user 4093 completed Excel Basics twice and the others on her team all took it once, but that doesn't result in a completion of >100% in the chart).

Edit: not all users will appear in the Completions table - in this example they all have a record, but 'Completed?' actually refers to whether another column (not shown here) indicates that they actually attended vs. withdrew from the class, didn't show up, was assigned but hasn't attended yet, etc.  So users who have never had anything to do with a training won't show up in Completions, but they need to be included in the overall percentage still.

'Completions' table

Training Title	User ID	Completed?
Excel Basics	4093	TRUE
Advanced Excel	4093	FALSE
PowerPoint Basics	4093	TRUE
Advanced PowerPoint	4093	TRUE
Excel Basics	4093	TRUE
Excel Basics	5185	TRUE
Advanced Excel	5185	TRUE
PowerPoint Basics	5185	FALSE
Advanced PowerPoint	5185	FALSE
Excel Basics	6660	FALSE
Advanced Excel	6660	FALSE
PowerPoint Basics	6660	TRUE
Advanced PowerPoint	6660	FALSE
Excel Basics	7981	TRUE
Advanced Excel	7981	TRUE
PowerPoint Basics	7981	TRUE
Advanced PowerPoint	7981	TRUE
Excel Basics	9890	FALSE
Advanced Excel	9890	FALSE
PowerPoint Basics	9890	FALSE
Advanced PowerPoint	9890	FALSE

'Users' table

User ID	User Manager ID	User Name	User Manager Name	User Status
4093	23186	Smith, Joe	Foo, Fred	Active
6660	84436	Doe, John	Bar, Beth	Active
7981	87582	Smith, Dave	Bar, Beth	Inactive
5185	66347	Smith, Agent	Foo, Fred	Active
9890	75308	Doe, Jane	Bar, Beth	Active

[v-sihou-msft · Accepted solution]

@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,