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. 

 

AssetTest DateResult XResult Y
Asset122.03.20182.2012.84
Asset122.03.20182.8012.80
Asset122.03.20182.6012.86
Asset220.03.20181.501.02
Asset220.03.20181.801.03
Asset220.03.20181.901.03
Asset320.03.20180.232.16
Asset320.03.20180.222.71
Asset320.03.20180.213.05
Asset120.01.20170.0017.62
Asset120.01.20170.0017.79
Asset120.01.20170.0017.51
Asset219.01.20170.394.85
Asset219.01.20170.395.15
Asset219.01.20170.385.36
Asset319.01.20170.1911.98
Asset319.01.20170.1911.97
Asset319.01.20170.1812.00

 

We want to present some logic where we rank each asset by the yearly test results. So, I want to present a table that looks like below (filtered by year in slicer). Its easy to present yearly average of the results, but I struggle to calculate the normalized values (normalized by the maximum value in the filtered column)  and then use the normalized values to calculate the last column: 

AssetYear

Result X

(average per year)

Result Y (average per year)Result X, Normalized of MaxResult Y, Normalized of MaxNorm Result X multiplied by  Norm Result Y
Asset120182.5312.83100%100%100%
Asset220181.731.0268%8%5%
Asset320180.222.649%21%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

2 Replies

  • 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? 

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    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