SUMX on a measure not working as intended

mark_carlisle — Fri, 20 Mar 2020 17:43:53 GMT

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

Re: SUMX on a measure not working as intended

Greg_Deckler — Fri, 20 Mar 2020 19:42:52 GMT

Perhaps:

Total Employees Hitting Target (0/1) = SUMX( FILTER(Lineup,NOT(ISBLANK([Has Target]))), [Is Hitting MTD Target (0/1)] )

Re: SUMX on a measure not working as intended

Anonymous — Fri, 20 Mar 2020 20:18:51 GMT

Here's your measure:

SUMX( values( Lineup[Level 6.FullName] ), [Is Hitting MTD Target (0/1)] )

Best

Re: SUMX on a measure not working as intended

mark_carlisle — Sun, 22 Mar 2020 19:07:25 GMT

This one worked.

Thank you for the assistance.

topic Re: SUMX on a measure not working as intended in DAX Commands and Tips

SUMX on a measure not working as intended

Re: SUMX on a measure not working as intended

Re: SUMX on a measure not working as intended

Re: SUMX on a measure not working as intended