Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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)
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 ) )
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |