cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Huiyan
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. 

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?

Huiyan_0-1624028405606.pngHuiyan_1-1624028446391.png

 

1 ACCEPTED SOLUTION
daxer
Solution Sage
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
            )
    )
)

 

View solution in original post

2 REPLIES 2
daxer
Solution Sage
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
            )
    )
)

 

View solution in original post

Huiyan
Regular Visitor

Thank you! @daxer 

Helpful resources

Announcements
MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors