Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Normalized column of max

I have a table of test results. Usually, there are three tests performed every year. Table below is input to my powerbi report.    Asset Test Date Result X Result Y Asset1 22.03.2018 2.2...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi Anonymous,

     

    Add a calculated column in source table.

    Year = YEAR(Sheet15[Test Date])

    Place below measures into table visual.

    Result X(average per year) = AVERAGE(Sheet15[Result X])
Result Y(average per year) = AVERAGE(Sheet15[Result Y])

Result X, Normalized of Max =
VAR maxAverage =
    MAXX (
        ALLSELECTED ( Sheet15 ),
        CALCULATE (
            AVERAGE ( Sheet15[Result X] ),
            ALLEXCEPT ( Sheet15, Sheet15[Asset], Sheet15[Year] )
        )
    )
RETURN
    [Result X(average per year)] / maxAverage

Result Y, Normalized of Max =
VAR maxAverage =
    MAXX (
        ALLSELECTED ( Sheet15 ),
        CALCULATE (
            AVERAGE ( Sheet15[Result Y] ),
            ALLEXCEPT ( Sheet15, Sheet15[Asset], Sheet15[Year] )
        )
    )
RETURN
    [Result Y(average per year)] / maxAverage

Norm Result X multiplied by  Norm Result Y = [Result X, Normalized of Max]*[Result Y, Normalized of Max]

     

    Best regards,

    Yuliana Gu

tex628's avatar
tex628
Icon for Community Champion rankCommunity Champion
7 years ago
Result X, Normalized of Max = 
VAR Year_ = 'Table'[Year]
'Table'[Result X (average per year)] / Calculate( Max('Table'[Result X (average per year)]) ; ALL('Table') ; 'Table'[Year] = Year_)

Something like this maybe?