[DAX] Sum top 3 values with a filter

[Anonymous]

Hello. I have been trying for the past day to solve this and could not get anywhere.

I recreated the data view into excel so I could change some sensitive data. Basically, the model gets data from multiple worksheets from multiple workbooks. In this query specifically, the model gets the percentage of ownership into the company (1 company = 1 workbook) for each one of the employees in that semester (the model is updated twice a year).

The worksheets are categorized. Suppose that worksheets 1, 2 and 3 belong to the same category. 

What I need to calculate is the average percentage that the top 3 shareholders have in that category:

-Filters by worksheet 1;
-Gets top 3 percentages in that worksheet and sums them; 
-Filters by worksheet 2;
-Gets top 3 percentages in that worksheet and sums them;
-...

I can later average them into the visual on the report view, but I am struggling to do both filters (by worksheet and top3). I've tried using CALCULATE, FILTER, TOPN, RANKX, SUMX...

Any help is appreciated! Thanks a lot.

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may refer to the post below.

https://community.powerbi.com/t5/Desktop/Show-Top-N-values-only-in-a-new-table-monthly-average-of-daily/m-p/233772#M104113