Issue with calculating average of TOPN average

PierreCome — Thu, 29 Apr 2021 11:04:38 GMT

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.

Re: Issue with calculating average of TOPN average

amitchandak — Thu, 29 Apr 2021 12:05:01 GMT

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/

Re: Issue with calculating average of TOPN average

PierreCome — Thu, 29 Apr 2021 13:17:20 GMT

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

topic Re: Issue with calculating average of TOPN average in DAX Commands and Tips

Issue with calculating average of TOPN average

Re: Issue with calculating average of TOPN average

Re: Issue with calculating average of TOPN average