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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
PierreCome
Regular Visitor

Issue with calculating average of TOPN average

Good day.

 

Being new to PBI I am struggling with calculating TOPN and would gladly accept your assistance.

 

I have a table that looks like this (4 years of data in total):

https://ibb.co/T8vFdzr

column A = month

column B = year

column I = week

column N = filter 1

column X = Data I want to average then topN

column AD = filter 2

 

Basically I want to be able to calculate the average per week of column X and be able to filter by column N and/or AD.

Then I get the top 5 weeks of a year and the average of that.

 

With visuals, I managed to obtain it:

https://ibb.co/QnHHtJh

 

But for the rest of the project, I need to calculate the difference between each week's average of column X and the average of the TOP 5 weeks.

Here is what it would look like in a matrix:

https://ibb.co/G5VFjRq

 

And I cannot find a way to calculate that "the average of the TOP 5 weeks.", which is visible in the visual:

https://ibb.co/x1fvGVy

 

Thank you for your assistance, I have been trying every possible solution I could find for the last days, but no success so far. And I am pretty sure this is quite simple.

 

Hope I was clear enough.

2 REPLIES 2
PierreCome
Regular Visitor

Thank you for your answer, I will try this and come back to you.

 

 

amitchandak
Super User
Super User

@PierreCome , You can create a rank like this and use visual level filter <=5

rankx(filter(allselected(Table[Year], Table[Week]), [Year] =max([year])), calculate(average(Table[X])),,desc, dense)

 

Also refer

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.