How to set the denominator to be used as a constant value for calculation

[Georgia_H]

Hello,

Can anyone please help. Thank you.

Objective: I want to calculate the Avg Compliance % of Metrics_ID divided by the constant Denominator (unique count by LBU & Metrics_ID i.e. 12)

Issue: I am unable to get the denominator as 12 to be applied in my division.

2 Measures derived:

I derived this measure to get the Distinctcount of Metrics_ID in the dataset (used as denominator)

Denominator =

VAR Count_PCM_denominator=CALCULATE(DISTINCTCOUNT('rep F_ITS_MetricsLanding'[Metrics_ID]),FILTER('rep F_ITS_MetricsLanding','rep F_ITS_MetricsLanding'[Metrics_ID] in {"PCM01","PCM03","PCM04","PCM09","PCM10","PCM19","PCM20","PCM23","PCM33","PCM34","PCM35","PCM36"}),ALL('rep F_ITS_MetricsLanding'[Metrics_ID],'rep F_ITS_MetricsLanding'[LBU]))

RETURN Count_PCM_denominator

Compliance_Value (AVG) =

VAR numerator=CALCULATE(AVERAGE('rep F_ITS_MetricsLanding'[Compliance_Value]))

RETURN DIVIDE(numerator,'rep F_ITS_MetricsLanding'[Denominator])

Expected Results:

Expected Results for EILUX
LBU	Metrics_ID	Avg Compliance %	Denominator (unique by Metrics_ID)	Expected Compliance_Value (AVG) divided by MAX Count of Denominator i.e. 12
EILUX	PCM23	60.10%	12	5.00%
				5.00%

Expected Results for PCALKK
LBU	Metrics_ID	Avg Compliance %	Denominator (unique by Metrics_ID)	Expected Compliance_Value (AVG) divided by MAX Count of Denominator i.e. 12
PCALKK	PCM04	100.00%	12	8.33%
				8.33%

Expected Results for PAMC
LBU	Metrics_ID	Avg Compliance %	Denominator (unique by Metrics_ID)	Expected Compliance_Value (AVG) divided by MAX Count of Denominator i.e. 12
PAMC	PCM01	78.80%	12	6.57%
PAMC	PCM03	81.30%	12	6.78%
PAMC	PCM04	62.10%	12	5.18%
PAMC	PCM09	56.80%	12	4.73%
PAMC	PCM10	59.60%	12	4.97%
PAMC	PCM19	68.60%	12	5.72%
PAMC	PCM20	55.90%	12	4.66%
PAMC	PCM23	85.90%	12	7.16%
PAMC	PCM33	65.00%	12	5.42%
PAMC	PCM34	47.80%	12	3.98%
PAMC	PCM35	57.10%	12	4.76%
PAMC	PCM36	62.00%	12	5.17%
				65.08%

Sample data:

LBU	Metrics_ID	Avg Compliance %	Denominator (unique by Metrics_ID)
EILUX	PCM23	60.10%	1
PAMC	PCM01	78.80%	1
PAMC	PCM03	81.30%	1
PAMC	PCM04	62.10%	1
PAMC	PCM09	56.80%	1
PAMC	PCM10	59.60%	1
PAMC	PCM19	68.60%	1
PAMC	PCM20	55.90%	1
PAMC	PCM23	85.90%	1
PAMC	PCM33	65.00%	1
PAMC	PCM34	47.80%	1
PAMC	PCM35	57.10%	1
PAMC	PCM36	62.00%	1
PCALKK	PCM04	100.00%	1
			12

[v-xiaotang · Accepted solution]

Hi @Georgia_H 

Thanks for reaching out to us.

You can try this measure,

Denominator = 

VAR Count_PCM_denominator=CALCULATE(DISTINCTCOUNT('rep F_ITS_MetricsLanding'[Metrics_ID]),FILTER(ALL('rep F_ITS_MetricsLanding'),'rep F_ITS_MetricsLanding'[Metrics_ID] in {"PCM01","PCM03","PCM04","PCM09","PCM10","PCM19","PCM20","PCM23","PCM33","PCM34","PCM35","PCM36"}))

RETURN Count_PCM_denominator

Best Regards,

Community Support Team _Tang

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

[lbendlin]

Read about  REMOVEFILTERS function (DAX) - DAX | Microsoft Docs