Wrong average number

[Huiyan]

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?

[Anonymous · Accepted 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
            )
    )
)

[Huiyan]

Thank you! @Anonymous