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.
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:
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
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 |
Solved! Go to 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.
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.
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 |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |