cancel
Showing results for
Did you mean:
Regular Visitor

## 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?

1 ACCEPTED SOLUTION
Solution Sage

``````Achievement_viz =
AVERAGEX(
VALUES( VIZ[Component] ),
CALCULATE(
var Cumulative_ =
SELECTEDVALUE( VIZ[Cumulative] )
var VizA = SUM( VIZ[A2020] )
var VizM = SUM( VIZ[M2020] )
var ComponentValue =
IF( Cumulative_ = 0,
DIVIDE( VizA, VizM ),
var VizBaseline =
SUM( VIZ[Baseline] )
var Result =
DIVIDE(
VizA - VizBaseline,
VizM - VizBaseline
)
return
Result
)
return
1.5 + min(
ComponentValue - 1.5,
0
)
)
)``````

2 REPLIES 2
Solution Sage

``````Achievement_viz =
AVERAGEX(
VALUES( VIZ[Component] ),
CALCULATE(
var Cumulative_ =
SELECTEDVALUE( VIZ[Cumulative] )
var VizA = SUM( VIZ[A2020] )
var VizM = SUM( VIZ[M2020] )
var ComponentValue =
IF( Cumulative_ = 0,
DIVIDE( VizA, VizM ),
var VizBaseline =
SUM( VIZ[Baseline] )
var Result =
DIVIDE(
VizA - VizBaseline,
VizM - VizBaseline
)
return
Result
)
return
1.5 + min(
ComponentValue - 1.5,
0
)
)
)``````

Regular Visitor

Thank you! @daxer

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.