Team coverage calculations

[mwebergo2]

I have a team coverage matrix that is used to evaluate proficiency in completing certain tasks amongst the team.  The proficiency is a ranking from 1 (no knowledge/experience with the task) to 5 (Expert level, could teach others).  Each team member has a proficiency score from 1-5 for every task.  

I am trying to create a dashboard that helps me understand coverage across the team for all the various work we do.  Most of this is pretty straight forward.  However, I am struggling to calculate 2 metrics, percentage of tasks with a single point of failure, and percentage of tasks where there is a gap.

More specifically, single point of failure is where there is only one team member who is ranked at 4 or 5 for a given task.  Gap is where there are zero team members with a ranking >= 3.

[bhanu_gautam · Accepted solution]

@mwebergo2 , Create a new column to identify if a task has a single point of failure. This column will count the number of team members with a proficiency of 4 or 5 for each task

DAX
SinglePointOfFailure =
CALCULATE(
COUNTROWS('YourTable'),
FILTER('YourTable', 'YourTable'[Proficiency] >= 4)
)

Create a measure to calculate the percentage of tasks with a single point of failure:

DAX
PercentageSinglePointOfFailure =
DIVIDE(
COUNTROWS(
FILTER(
SUMMARIZE('YourTable', 'YourTable'[Task], "CountHighProficiency", [SinglePointOfFailure]),
[CountHighProficiency] = 1
)
),
COUNTROWS(SUMMARIZE('YourTable', 'YourTable'[Task])),
0
)

Create a new column to identify if a task has a gap. This column will count the number of team members with a proficiency of 3 or higher for each task.

DAX
Gap =
CALCULATE(
COUNTROWS('YourTable'),
FILTER('YourTable', 'YourTable'[Proficiency] >= 3)
)

DAX
PercentageGap =
DIVIDE(
COUNTROWS(
FILTER(
SUMMARIZE('YourTable', 'YourTable'[Task], "CountProficiency", [Gap]),
[CountProficiency] = 0
)
),
COUNTROWS(SUMMARIZE('YourTable', 'YourTable'[Task])),
0
)

Use the measures PercentageSinglePointOfFailure and PercentageGap to create visuals in your Power BI dashboard to display these metrics.

[mwebergo2]

Perfect!  Thank you!

[bhanu_gautam]

You are welcome 

[mwebergo2]

Forgot to include my data:  

Task	Category	Sub-Category	Team Member	Proficiency
Alternative Report	Reporting	Performance Rptg	Team Member 1	5
Agency Rating Check	Data Governance	SMF Data Validation	Team Member 1	3
Analyst Check	Data Governance	SMF Data Validation	Team Member 1	3
AdTrax Marketing Review	Reporting	Compliance Reporting	Team Member 1	3
Alternative Report	Reporting	Performance Rptg	Team Member 2	2
Agency Rating Check	Data Governance	SMF Data Validation	Team Member 2	1
Analyst Check	Data Governance	SMF Data Validation	Team Member 2	1
AdTrax Marketing Review	Reporting	Compliance Reporting	Team Member 2	5
Alternative Report	Reporting	Performance Rptg	Team Member 3	1
Agency Rating Check	Data Governance	SMF Data Validation	Team Member 3	5
Analyst Check	Data Governance	SMF Data Validation	Team Member 3	5
AdTrax Marketing Review	Reporting	Compliance Reporting	Team Member 3	1
Alternative Report	Reporting	Performance Rptg	Team Member 4	1
Agency Rating Check	Data Governance	SMF Data Validation	Team Member 4	4
Analyst Check	Data Governance	SMF Data Validation	Team Member 4	5
AdTrax Marketing Review	Reporting	Compliance Reporting	Team Member 4	1