Sum or count of a measure - unable to sum a Measure or do a count of a measure

[jagbatth]

Hello,

I need some help calculating the sum a measure I created using DAX.

I caclulated the Thershold measure = 1-error rate

Error Rate measure = SUM of 'TABLE'[Errors] / Count 'TABLE'[STATE]

lastly for the Threshold > 97% measure = if([Threshold]>.97, 1, 0)

To give me the below Matrix view, 

Now when I try to creat a Measure that SUMS up the "1"s in the [Threshold > 97%] measure, I am not allowed?

SUM of states greater than 97% threshold = SUM([Threshold > 97%])

It wont let me input the Measure I created, I can only see it in the visual above but can not use it to show other metrics.

I tried creating a column instead and then it gives a CYCLIC REFERENCE error.

I get the same thing when trying to COUNT([Threshold > 97%]), as I need to figure out the % Met Threshold which would be:

SUM([Threshold > 97%]) / COUNT([Threshold > 97%])

Any help is mych appreciated!

[Anonymous · Accepted solution]

Hi @jagbatth 

You can try the following steps:

You create several measures as follow.

Error Rate = DIVIDE(SELECTEDVALUE('Table'[Errors]), SELECTEDVALUE('Table'[Claim Count]))

Thershold = 1 - [Error Rate]

Threshold > 97% = IF( [Thershold] > 0.97, 1, 0)

count1 = COUNTX( FILTER( 'Table', [Threshold > 97%] = 1), [Threshold > 97%])

percent = [count1] / COUNT('Table'[Product])

Is this the result you expect?

Best Regards,
Community Support Team _Yuliax

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ritaf1983]

Hi @jagbatth 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[jagbatth]

System	Product	State	Claim Count	Error Rate	Errors	Threshold	X>97% Threshold
SAMPLE	Market	FL	1398	6.01%	84	94.00%	0
SAMPLE	Market	MO	1200	5.75%	69	94.30%	0
SAMPLE	CARE	OH	1171	7.69%	90	92.30%	0
SAMPLE	CARE	SC	1048	0.38%	4	99.60%	1
SAMPLE	Market	GA	1356	11.06%	150	88.90%	0
SAMPLE	Market	NC	1072	4.01%	43	96.00%	0
SAMPLE	Market	TX	1297	6.78%	88	93.20%	0
SAMPLE	Market	MI	1121	1.07%	12	98.90%	1
SAMPLE	Market	MS	1206	6.30%	76	93.70%	0
SAMPLE	Market	IN	1128	2.84%	32	97.20%	1
SAMPLE	Market	AR	1245	5.30%	66	94.70%	0
SAMPLE	CARE	AZ	1072	0.28%	3	99.70%	1
SAMPLE	CARE	CA	1249	0.72%	9	99.30%	1
SAMPLE	Market	OH	1136	6.95%	79	93.00%	0
SAMPLE	CARE	OK	1073	0.19%	2	99.80%	1
SAMPLE	Market	TN	1238	6.06%	75	93.90%	0
SAMPLE	Market	KS	1082	1.76%	19	98.20%	1
SAMPLE	Market	AZ	1130	2.04%	23	98.00%	1
SAMPLE	CARE	WI	1087	5.52%	60	94.50%	0
SAMPLE	Market	SC	1167	1.29%	15	98.70%	1
SAMPLE	Market	IL	1047	2.48%	26	97.50%	1
SAMPLE	CARE	NE	1115	0.72%	8	99.30%	1
SAMPLE	CARE	OR	1091	1.01%	11	99.00%	1
SAMPLE	CARE	PA	1110	0.36%	4	99.60%	1
SAMPLE	Market	KY	1119	0.89%	10	99.10%	1
SAMPLE	Market	NE	1166	1.11%	13	98.90%	1
SAMPLE	CARE	NV	1046	0.67%	7	99.30%	1
SAMPLE	Market	PA	1078	1.95%	21	98.10%	1
SAMPLE	Market	NV	1081	1.02%	11	99.00%	1
SAMPLE	Market	WA	1159	0.43%	5	99.60%	1
SAMPLE	CARE	MO	1090	0.18%	2	99.80%	1
SAMPLE	CARE	KS	1116	0.36%	4	99.60%	1
SAMPLE	CARE	IN	1070	1.59%	17	98.40%	1
SAMPLE	CARE	TX	1030	0.58%	6	99.40%	1
SAMPLE	CARE	NM	1068	0.09%	1	99.90%	1
SAMPLE	CARE	GA	574	0.17%	1	99.80%	1
SAMPLE	CARE	MS	1068	0.09%	1	99.90%	1
SAMPLE	CARE	FL	616	0.16%	1	99.80%	1
SAMPLE	CARE	AR	1108	0.18%	2	99.80%	1
SAMPLE	CARE	IL	1	0.00%	0	100.00%	1
						SUM:	29
						Count:	40
						sum/count:	73%
						For SAMPLE SYSTEM	73% met the Thershold

How Do I get the SUM and Count using DAX, above is a excel where it is easy to do, my final goal is to get to that 73% met the threshold but in PBI, I cannot sum or count that measure to be able to evetually divde and get a percentage.

[Ashish_Mathur]

Hi,

Try these measures

Measure = countrows(Data)

Measure 1 = calculate([Measure],data[Threshold]>0.97)

Measure 3 = divide([Measure 1],[Measure])

Hope this helps.