Coverage Calculation based on the plan table and data table

To calculate the coverage of targeted accounts per employee based on your two tables, you can set up a few DAX measures to evaluate whether each account in the plan has received any activity, then calculate the coverage rate based on the results.

Here’s how you can achieve this:

Step 1: Understand the Data Structure and Relationships
Let’s assume the following:
- Plan Table: Contains one row per employee and account combination, showing each employee’s targeted accounts.
- Columns: `EmployeeID`, `AccountID`, `Open Count`
- `Open Count = 0` indicates no activity, while any other number indicates activity.

- Activity Table: Contains rows for each instance of activity, with duplicate `AccountID` values, since multiple employees can work with the same account.
- Columns: `EmployeeID`, `AccountID`, `ActivityDetails`

There’s a one-to-many relationship from `Plan Table[AccountID]` to `Activity Table[AccountID]`.

Step 2: Create a Measure to Count Targeted Accounts with Activity
1. Total Targeted Accounts: First, calculate the total number of targeted accounts for each employee in the plan table.

DAX

TotalTargetedAccounts =
CALCULATE(
COUNTROWS('Plan Table'),
ALLEXCEPT('Plan Table', 'Plan Table'[EmployeeID])
)

2. Total Covered Accounts: Then, calculate the count of accounts with activity (where `Open Count` is greater than 0) for each employee.

DAX

TotalCoveredAccounts =
CALCULATE(
COUNTROWS('Plan Table'),
ALLEXCEPT('Plan Table', 'Plan Table'[EmployeeID]),
'Plan Table'[Open Count] > 0
)

3. Coverage Rate: Finally, calculate the coverage rate as the ratio of `TotalCoveredAccounts` to `TotalTargetedAccounts`.

DAX

CoverageRate =
DIVIDE([TotalCoveredAccounts], [TotalTargetedAccounts], 0)

This measure will return the percentage of targeted accounts that have been covered for each employee.

Step 3: Display in Power BI
1. In a Table visual, add `EmployeeID` from the Plan Table.
2. Add the `TotalTargetedAccounts`, `TotalCoveredAccounts`, and `CoverageRate` measures to the table to show each employee’s coverage.

Step 4: Additional Customization (Optional)
If you want to drill down into specific details for each account or create filters, you can add a slicer for `EmployeeID` or `AccountID` to refine the analysis.

This setup should give you a clear view of each employee’s coverage based on the targeted accounts in the plan and whether activities were conducted. Let me know if you need further adjustments or if the data structure has any additional complexities!

Please mark this as solution if this helps you. Kudos appreciated

Thank you, i just sovled with very similar approach with help of ChaptGPt 🙂

UniqueAccountsInPlan := CALCULATE(
DISTINCTCOUNT(PlanTable[AccountID]),
ALLEXCEPT(PlanTable, PlanTable[EmployeeID])
)

UniqueAccountsWithActivity := CALCULATE(
DISTINCTCOUNT(PlanTable[AccountID]),
FILTER(
PlanTable,
RELATED(ActivityTable[Open count]) <> 0
)
)

CoveragePerEmployee := DIVIDE(
[UniqueAccountsWithActivity],
[UniqueAccountsInPlan],
0
)