Forum Discussion
Team coverage calculations
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.
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 |