Forum Discussion

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

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
        )
    )

Resident Rockstar

8 years ago

Can you share your expected result for better understanding?

Regular Visitor

8 years ago

Hi, here is the expected result. Does this help? Thanks!

Team	Year	Rating_Type	Rating	Rating_Normalized_by_Year_1
A	1	Happiness	5	=5/5=100%
A	1	Quality	4.5	=4.5/4.5=100%
A	2	Happiness	5	=5/5=100%
A	2	Quality	5	=5/4.5=111%
B	1	Happiness	4.5	=4.5/4.5=100%
B	1	Quality	5	=5/5=100%
B	2	Happiness	4.5	=4.5/4.5=100%
B	2	Quality	5	=5/5=100%
C	1	Happiness	4	=4/4=100%
C	1	Quality	5	=5/5=100%
C	2	Happiness	3	=3/4=75%
C	2	Quality	5	=5/5=100%

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