Forum Discussion
Team coverage calculations
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.
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.
4 Replies
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 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.
Perfect! Thank you!
You are welcome
