topic Re: Team coverage calculations in DAX Commands and Tips

Team coverage calculations

mwebergo2 — Mon, 06 Jan 2025 14:46:38 GMT

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.

Re: Team coverage calculations

mwebergo2 — Mon, 06 Jan 2025 14:49:18 GMT

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

Re: Team coverage calculations

bhanu_gautam — Mon, 06 Jan 2025 15:14:33 GMT

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.

Re: Team coverage calculations

mwebergo2 — Mon, 06 Jan 2025 16:37:02 GMT

Perfect! Thank you!

Re: Team coverage calculations

bhanu_gautam — Mon, 06 Jan 2025 17:44:22 GMT

You are welcome