Calculated Column (From Month Values to Average Quarter Values)

[qwertzuiop]

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

[FreemanZ]

hi @qwertzuiop 

try like:

NewColumn =

AVERAGEX(

   FILTER(

         TableName,

         TableName[Product]=EARLIER(TableName[Product])&&TableName[Quarter]=EARLIER(TableName[Quarter])

    ),

    TableName[Rating (Monthly)]

)

[ribisht17]

@qwertzuiop 

Please use this DAX

Average Column = CALCULATE(AVERAGE('Average Rating'[Rating (Monthly)]),ALLEXCEPT('Average Rating','Average Rating'[Product],'Average Rating'[Quarter]))

Regards,

Ritesh

[qwertzuiop]

Thank 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
qwertzuiop

[ribisht17 · Accepted solution]

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