Forum Discussion
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...
HI kmo
Try this column
Column = Table1[Rating] / CALCULATE ( SUM ( Table1[Rating] ), FILTER ( ALLEXCEPT ( Table1, Table1[Team], Table1[Rating_Type] ), Table1[Year] = 1 ) )
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% |
HI kmo
Try this column
Column =
Table1[Rating]
/ CALCULATE (
SUM ( Table1[Rating] ),
FILTER (
ALLEXCEPT ( Table1, Table1[Team], Table1[Rating_Type] ),
Table1[Year] = 1
)
)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?
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