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

     

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

 

Huiyan's avatar
Huiyan
Regular Visitor
5 years ago

Thank you! Anonymous