Coverage Calculation based on the plan table and data table

[al1981]

Dear all

I am kindly asking for help. I have two tables:

Table of our employees with plan - targeted accounts with ID. This ID is linked to activity table, but ID in 2nd table is not unique as it can happen that two employees have same account in target. In 1st table i have ID (its unique here) and in column "Open count"  i have numbers - 0 means no activity, but anything else means activity was done, no matter how many. Relation ship one to many is done for ID from 1st to 2nd table

Based on this I would like to calculate coverage for plan per employee. May I kindly ask for some hint? I tried few, but failed ....

[FarhanJeelani · Accepted solution]

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

[al1981]

Dear @FarhanJeelani 

I just saw yours is not exact as PLAN table does not have Count colum - only 1st table has it (activity table), so your 2nd DAX is actually not ok, should be like this

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

[al1981 · Accepted solution]

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
)

[FarhanJeelani · Accepted solution]

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