Forum Discussion

kmo's avatar
kmo
Regular Visitor
8 years ago
Solved

Normalize row based on another row's value

Hi,   I have a time series table like this one, and what I want to do is calculate a normalized Rating based on the relevant Rating for each Team and Rating_Type in year 1. To be clear, I mean I wa...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    HI kmo

     

    Try this column

     

    Column =
Table1[Rating]
    / CALCULATE (
        SUM ( Table1[Rating] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Team], Table1[Rating_Type] ),
            Table1[Year] = 1
        )
    )
afzalphatan's avatar
afzalphatan
Resolver I
8 years ago

You can use the below formula for your requirement 

=
Var Denominator = CALCULATE(SUM(Table2[Rating]), CALCULATETABLE(Table2, ALLEXCEPT(Table2, Table2[Team], Table2[Rating_Type]), Table2[Year] = 1))
Return
DIVIDE(Table2[Rating], Denominator)