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.
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?
Solved! Go to Solution.
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
)
)
)
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
)
)
)
Thank you! @Anonymous
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 |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |