cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## 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

1 ACCEPTED SOLUTION
Super User

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 4
Super User

try like:

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

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

Regards,
Ritesh

Thank you so much for your support @ribisht17

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

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors