Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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):
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:
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:
And I cannot find a way to calculate that "the average of the TOP 5 weeks.", which is visible in the visual:
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.
Thank you for your answer, I will try this and come back to you.
@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/
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |