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
        )
    )
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
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
        )
    )
kmo's avatar
kmo
Regular Visitor
8 years ago

Thank you. As a new user, I'm sure I am missing something.

I get the error "Expression.Error: The name 'CALCULATE' wasn't recognized. Make sure it's spelled correctly."

 

I was trying to put your formula into the Query Editor --> Custom column box. It seems that DAX formulas (I think that's what this is) don't work there. Is there somewhere else I should be putting this code?

 

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    HI kmo

     

    Yes you are right... this won't work in Query Editor.. since it is a calculated column (different than a custom column)

     

    Exit the Query Editor>>>Go to modelling Tab>>>New Column>>then use this formula