Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Here's your measure:
SUMX(
values( Lineup[Level 6.FullName] ),
[Is Hitting MTD Target (0/1)]
)
Best
D
Here's your measure:
SUMX(
values( Lineup[Level 6.FullName] ),
[Is Hitting MTD Target (0/1)]
)
Best
D
This one worked.
Thank you for the assistance.
Perhaps:
Total Employees Hitting Target (0/1) =
SUMX( FILTER(Lineup,NOT(ISBLANK([Has Target]))), [Is Hitting MTD Target (0/1)] )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |