Forum Discussion

Huiyan's avatar
Huiyan
Regular Visitor
5 years ago
Solved

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. 

ComponentCountryBaselineM2020A2020CumulativeSP Output
3.3.2.2.1BDI01113.3.2
3.3.2.2.1CAF01113.3.2
3.3.2.2.1CIV11113.3.2
3.3.2.2.1COD01113.3.2
3.3.2.2.1GHA11113.3.2
3.3.2.2.1GMB01113.3.2
3.3.2.2.1LBR11113.3.2
3.3.2.2.1MWI01113.3.2
3.3.2.2.1SLE00013.3.2
3.3.2.2.1UGA11113.3.2
3.3.2.2.1ZAF11113.3.2
3.3.2.2.1ZWE11113.3.2
3.3.2.2.1AFG00013.3.2
3.3.2.2.1BGD01113.3.2
3.3.2.2.1PAK01113.3.2
3.3.2.2.1PNG11113.3.2
3.3.2.2.1DZA01113.3.2
3.3.2.2.1IRQ11113.3.2
3.3.2.2.1LBN11113.3.2
3.3.2.2.1LBY11113.3.2
3.3.2.2.1PAL01113.3.2
3.3.2.2.1SOM01113.3.2
3.3.2.2.1TUN11113.3.2
3.3.2.2.1BIH01113.3.2
3.3.2.2.1GEO01113.3.2
3.3.2.2.1UKR01113.3.2
3.3.2.2.1BOL01113.3.2
3.3.2.2.1BRA01113.3.2
3.3.2.2.1COL01113.3.2
3.3.2.2.1GTM01113.3.2
3.3.2.2.1PER11113.3.2
3.3.2.2.1SLV11113.3.2
3.3.2.1.BAGO37405413.3.2
3.3.2.1.BCAF7513113613.3.2
3.3.2.1.BCOG023013.3.2
3.3.2.1.BGMB615813.3.2
3.3.2.1.BLSO0153613.3.2
3.3.2.1.BMWI16213.3.2
3.3.2.1.BNER16529828613.3.2
3.3.2.1.BSLE10410410413.3.2
3.3.2.1.BZAF12121213.3.2
3.3.2.1.BAFG7310710213.3.2
3.3.2.1.BBGD0404613.3.2
3.3.2.1.BDZA015813.3.2
3.3.2.1.BYEM0602513.3.2
3.3.2.1.BKGZ23282113.3.2
3.3.2.1.BUKR363658913.3.2
3.3.2.1.BBRA30404013.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?

 

help fix
Help Requested
  • Anonymous's avatar
    Anonymous
    5 years ago

     

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

     

    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
            )
    )
)