Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
qwertzuiop
Advocate III
Advocate III

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

ProductMonthRating (Monthly)QuarterAVG Rating (Quarterly) 
A01.01.20224.51 
A01.02.20224.61 
A01.03.20224.714.6
A01.04.20224.42 
A01.05.20224.22 
A01.06.20223.824.1
B01.01.20222.51 
B01.02.20222.91 
B01.03.20222.412.6
B01.04.20222.72 
B01.05.20222.22 
B01.06.20221.922.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)

qwertzuiop_0-1675077918441.png


Thank you very much for your contribution

Cheers

qwertzuiop

1 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)

ribisht17_0-1675084864746.png

With line it can give a false picture

ribisht17_0-1675090565977.png

 

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

 

View solution in original post

4 REPLIES 4
FreemanZ
Super User
Super User

hi @qwertzuiop 

try like:

NewColumn =
AVERAGEX(
   FILTER(
         TableName,
         TableName[Product]=EARLIER(TableName[Product])&&TableName[Quarter]=EARLIER(TableName[Quarter])
    ),
    TableName[Rating (Monthly)]
)
ribisht17
Super User
Super User

@qwertzuiop 

 

Please use this DAX

Average Column = CALCULATE(AVERAGE('Average Rating'[Rating (Monthly)]),ALLEXCEPT('Average Rating','Average Rating'[Product],'Average Rating'[Quarter]))
ribisht17_0-1675079569794.png

 


 

Regards,
Ritesh

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

qwertzuiop_0-1675079750618.png
As an example
Don't like that Quarter has also 3 values per Month

qwertzuiop_1-1675079987678.png

 



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)

ribisht17_0-1675084864746.png

With line it can give a false picture

ribisht17_0-1675090565977.png

 

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.