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 want to divide Rating by the year 1 value of Rating for each Year, Team, and Rating_type.
| Team | Year | Rating_Type | Rating |
| A | 1 | Happiness | 5 |
| A | 1 | Quality | 4.5 |
| A | 2 | Happiness | 5 |
| A | 2 | Quality | 5 |
| B | 1 | Happiness | 4.5 |
| B | 1 | Quality | 5 |
| B | 2 | Happiness | 4.5 |
| B | 2 | Quality | 5 |
| C | 1 | Happiness | 4 |
| C | 1 | Quality | 5 |
| C | 2 | Happiness | 3 |
| C | 2 | Quality | 5 |
I've tried this a few different ways but none seem to work. I'm new to Power BI and I think I am still thinking of this in terms of how I'd do the calculation in excel, SQL, etc. Help me think in a Power BI way, please. Thanks.
HI kmo
Try this column
Column = Table1[Rating] / CALCULATE ( SUM ( Table1[Rating] ), FILTER ( ALLEXCEPT ( Table1, Table1[Team], Table1[Rating_Type] ), Table1[Year] = 1 ) )
6 Replies
Can you share your expected result for better understanding?
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 ) )
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)
