Normalize row based on another row's value

[kmo]

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.

[afzalphatan]

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)

[SivaMani]

Can you share your expected result for better understanding?

[kmo]

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 · Accepted solution]

HI @kmo

Try this column

Column =
Table1[Rating]
    / CALCULATE (
        SUM ( Table1[Rating] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Team], Table1[Rating_Type] ),
            Table1[Year] = 1
        )
    )

[kmo]

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]

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