Normalized column of max

[Anonymous]

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.20	12.84
Asset1	22.03.2018	2.80	12.80
Asset1	22.03.2018	2.60	12.86
Asset2	20.03.2018	1.50	1.02
Asset2	20.03.2018	1.80	1.03
Asset2	20.03.2018	1.90	1.03
Asset3	20.03.2018	0.23	2.16
Asset3	20.03.2018	0.22	2.71
Asset3	20.03.2018	0.21	3.05
Asset1	20.01.2017	0.00	17.62
Asset1	20.01.2017	0.00	17.79
Asset1	20.01.2017	0.00	17.51
Asset2	19.01.2017	0.39	4.85
Asset2	19.01.2017	0.39	5.15
Asset2	19.01.2017	0.38	5.36
Asset3	19.01.2017	0.19	11.98
Asset3	19.01.2017	0.19	11.97
Asset3	19.01.2017	0.18	12.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: 

Asset	Year	Result X (average per year)	Result Y (average per year)	Result X, Normalized of Max	Result Y, Normalized of Max	Norm Result X multiplied by  Norm Result Y
Asset1	2018	2.53	12.83	100%	100%	100%
Asset2	2018	1.73	1.02	68%	8%	5%
Asset3	2018	0.22	2.64	9%	21%	2%

[v-yulgu-msft · Accepted solution]

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]

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?