I want to be able to calculate the percentage of agents within a team hitting target.
Only agents that have a target should be include in the calculation. This is how I would like the data to display in a table format;
| Level 5.FullName | Level 6.FullName | Closed Won Referrals | Target To Date | Employee Count | Has Target | Total Employees With A Target | Is Hitting MTD Target (0/1) | Total Employees Hitting Target (0/1) | Achieving MTD Target (%) |
| Manager LR | Agent GD | 4 | 2 | 1 | 1 | 1 | 1 | 1 | 100.00% |
| Manager LR | Agent GS | 11 | 10 | 1 | 1 | 1 | 1 | 1 | 100.00% |
| Manager LR | Agent X | 2 | | 1 | | | | | |
| Totals | | 17 | 12 | 3 | 1 | 2 | 1 | 2 | 100.00% |
In the above example Agent X would not be included in the calculation for Achieving MTD Target (%) as they do not have a target. The columns highlighted in Orange are all measures.
My issue is that the measure Total Employees Hitting Target (0/1) is not working as expect. The result of the measures is shown below.
| Level 5.FullName | Level 6.FullName | Closed Won Referrals | Target To Date | Employee Count | Has Target | Total Employees With A Target | Is Hitting MTD Target (0/1) | Total Employees Hitting Target (0/1) | Achieving MTD Target (%) |
| Manager LR | Agent GD | 4 | 2 | 1 | 1 | 1 | 1 | 1 | 100.00% |
| Manager LR | Agent GS | 11 | 10 | 1 | 1 | 1 | 1 | 0 | 0.00% |
| Totals | | 15 | 11 | 2 | 1 | 2 | 1 | 1 | 50.00% |
As can be seen in the columns in Red, Agent GS has 11 Closed Won Referrals to Date and a target of 10 therefore Total Employees Hitting Target (0/1) should be 1 not 0. The DAX for this measure is;
Total Employees Hitting Target (0/1) =
SUMX( Lineup, [Is Hitting MTD Target (0/1)] )
And as can be seen Is Hitting MTD Target (0/1) is calculating correctly.
I have a sample dataset on OneDrive (link below) that reflects what is happening in the real dataset to assist with troubleshooting. I'm at a loss as to why this is not working when I have used SUMX to sum the measures in the past, so any assistance is greatly appreciated.
https://1drv.ms/u/s!AkFN9EElJAPfjzFsmSO9zKHXrgAP?e=dLC0t2
