Forum Discussion
Calculated Column (From Month Values to Average Quarter Values)
Dear Power BI-Community
Following problem to solve:
Let's assume this table:
Rating for Products
| Product | Month | Rating (Monthly) | Quarter | AVG Rating (Quarterly) |
| A | 01.01.2022 | 4.5 | 1 | |
| A | 01.02.2022 | 4.6 | 1 | |
| A | 01.03.2022 | 4.7 | 1 | 4.6 |
| A | 01.04.2022 | 4.4 | 2 | |
| A | 01.05.2022 | 4.2 | 2 | |
| A | 01.06.2022 | 3.8 | 2 | 4.1 |
| B | 01.01.2022 | 2.5 | 1 | |
| B | 01.02.2022 | 2.9 | 1 | |
| B | 01.03.2022 | 2.4 | 1 | 2.6 |
| B | 01.04.2022 | 2.7 | 2 | |
| B | 01.05.2022 | 2.2 | 2 | |
| B | 01.06.2022 | 1.9 | 2 | 2.3 |
Do you guys know a way to add a calculated column in Power BI which calculates the quaterly average?
Finally I would like to create such a visual (Montly and AVG Quarterly combined)
Thank you very much for your contribution
Cheers
qwertzuiop
Would you like to try with Bar Chart ?
For comparision Bar can be a good option here
Solution = IF( CALCULATE(MAX('Average Rating'[Month]),ALLEXCEPT('Average Rating','Average Rating'[Quarter]))='Average Rating'[Month],'Average Rating'[Average Column],0)With line it can give a false picture
Regards,
Ritesh
Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !! PL 300 Certification Series
4 Replies
Please use this DAX
Average Column = CALCULATE(AVERAGE('Average Rating'[Rating (Monthly)]),ALLEXCEPT('Average Rating','Average Rating'[Product],'Average Rating'[Quarter]))
Regards,RiteshThank you so much for your support ribisht17
I will accept your answer as solution.
Maybe you can help me out a bit more.
Is there a way to set all the non marked cell to blank.
In this case i would only have one value per quarter
As an example
Don't like that Quarter has also 3 values per Month
Cheers
qwertzuiopWould you like to try with Bar Chart ?
For comparision Bar can be a good option here
Solution = IF( CALCULATE(MAX('Average Rating'[Month]),ALLEXCEPT('Average Rating','Average Rating'[Quarter]))='Average Rating'[Month],'Average Rating'[Average Column],0)With line it can give a false picture
Regards,
Ritesh
Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !! PL 300 Certification Series
hi qwertzuiop
try like:
NewColumn =AVERAGEX(FILTER(TableName,TableName[Product]=EARLIER(TableName[Product])&&TableName[Quarter]=EARLIER(TableName[Quarter])),TableName[Rating (Monthly)])
