Wrong average number
Dear fellows, Can you kindly help me to check how to get the desired value of "125%"? My data: there are 2 components 3.3.2.2.1 and 3.3.2.1.B, they all belong to SP 3.3.2. First, I calculate the achievement at component level, (sum(A2020)-sum(Baseline))/(sum(M2020)-sum(Baseline)), if they are bigger than 150%, we set it at 150%. So here we got 150% for 3.3.2.1.B, and 100% for 3.3.2.2.1. In the end, we calculate the average of the two, it is 125% for SP 3.3.2. Component Country Baseline M2020 A2020 Cumulative SP Output 3.3.2.2.1 BDI 0 1 1 1 3.3.2 3.3.2.2.1 CAF 0 1 1 1 3.3.2 3.3.2.2.1 CIV 1 1 1 1 3.3.2 3.3.2.2.1 COD 0 1 1 1 3.3.2 3.3.2.2.1 GHA 1 1 1 1 3.3.2 3.3.2.2.1 GMB 0 1 1 1 3.3.2 3.3.2.2.1 LBR 1 1 1 1 3.3.2 3.3.2.2.1 MWI 0 1 1 1 3.3.2 3.3.2.2.1 SLE 0 0 0 1 3.3.2 3.3.2.2.1 UGA 1 1 1 1 3.3.2 3.3.2.2.1 ZAF 1 1 1 1 3.3.2 3.3.2.2.1 ZWE 1 1 1 1 3.3.2 3.3.2.2.1 AFG 0 0 0 1 3.3.2 3.3.2.2.1 BGD 0 1 1 1 3.3.2 3.3.2.2.1 PAK 0 1 1 1 3.3.2 3.3.2.2.1 PNG 1 1 1 1 3.3.2 3.3.2.2.1 DZA 0 1 1 1 3.3.2 3.3.2.2.1 IRQ 1 1 1 1 3.3.2 3.3.2.2.1 LBN 1 1 1 1 3.3.2 3.3.2.2.1 LBY 1 1 1 1 3.3.2 3.3.2.2.1 PAL 0 1 1 1 3.3.2 3.3.2.2.1 SOM 0 1 1 1 3.3.2 3.3.2.2.1 TUN 1 1 1 1 3.3.2 3.3.2.2.1 BIH 0 1 1 1 3.3.2 3.3.2.2.1 GEO 0 1 1 1 3.3.2 3.3.2.2.1 UKR 0 1 1 1 3.3.2 3.3.2.2.1 BOL 0 1 1 1 3.3.2 3.3.2.2.1 BRA 0 1 1 1 3.3.2 3.3.2.2.1 COL 0 1 1 1 3.3.2 3.3.2.2.1 GTM 0 1 1 1 3.3.2 3.3.2.2.1 PER 1 1 1 1 3.3.2 3.3.2.2.1 SLV 1 1 1 1 3.3.2 3.3.2.1.B AGO 37 40 54 1 3.3.2 3.3.2.1.B CAF 75 131 136 1 3.3.2 3.3.2.1.B COG 0 2 30 1 3.3.2 3.3.2.1.B GMB 6 15 8 1 3.3.2 3.3.2.1.B LSO 0 15 36 1 3.3.2 3.3.2.1.B MWI 1 6 2 1 3.3.2 3.3.2.1.B NER 165 298 286 1 3.3.2 3.3.2.1.B SLE 104 104 104 1 3.3.2 3.3.2.1.B ZAF 12 12 12 1 3.3.2 3.3.2.1.B AFG 73 107 102 1 3.3.2 3.3.2.1.B BGD 0 40 46 1 3.3.2 3.3.2.1.B DZA 0 15 8 1 3.3.2 3.3.2.1.B YEM 0 60 25 1 3.3.2 3.3.2.1.B KGZ 23 28 21 1 3.3.2 3.3.2.1.B UKR 36 36 589 1 3.3.2 3.3.2.1.B BRA 30 40 40 1 3.3.2 My formula is as follows: Achievement_viz = VAR one = AVERAGEX(VALUES(VIZ[Component]), CALCULATE ( IF ( SELECTEDVALUE(VIZ[Cumulative]) = 0, SUM ( VIZ[A2020] ) / SUM ( VIZ[M2020] ), ( ( SUM ( VIZ[A2020] ) - SUM ( VIZ[Baseline] ) ) / ( SUM ( VIZ[M2020] ) - SUM ( VIZ[Baseline] ) ) ) ) )) RETURN IF ( one >= 1.5, 1.5, one ) Currently, PowerBI gave me the result of "150%". Do you know how to solve it?
