TOP N based on column

[Anonymous]

Hi Experts,

Sorry I tried to ask question here but probably I did not attriculate it well.

https://community.powerbi.com/t5/Desktop/Top-N-from-the-column/m-p/2531292#M897722

I am looking for TOP N of Different columns based on the Reasontype.

How to achieve this ?

Thanks a lot

[isabella]

Hi @Anonymous 

Top3 dept by reason and top 3 name by reason should be deal with seperately

Here are my steps，my approach is to filter the top3 reason ，then

Add column = Count the dept by reason ，then rank by reason and this  column

copy the table in power query

group by reason

sort count

Now the data looks as：

We can delete the bottom 2 row

Now the table looks as：

We can copy the table here ，for one to calculate to top 3 dept， the other one to calculate the top name

Then expand the dept column

remove the Count column

Then  group by reason and department

Then sort the reason

Then sort    the Count column

Then add index column( I need to explain why the index column is added here, because some counts are the same number, and if they are sorted by rankx later, they are also the same serial number, which cannot be distinguished, so I choose to add an index column, and then sum with the count, so that Can each department correspond to one unique sort number )

Sum the Count and index column

Delete the count and index column

Close and apply ，back to power bi desktop

Add column

rank by department = RANKX(FILTER( 'Table','Table'[Reason]=EARLIER('Table'[Reason])),'Table'[Addition],,,Dense)

In visualization

The above are the steps to obtain top 3 department by reason, and the same is true for obtaining top 3 name by reason .

Best Regards,

Community Support Team _Isabella

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , refer this

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

A meausre like this should help

calculate(count(Table[name]) ,TOPN(3,all(Table[Department]),calculate(count(Table[name]) )), values(Table[Department]))