cancel
Showing results for
Search instead for
Did you mean:
Anonymous
Not applicable

## TOP N based on column

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

2 REPLIES 2
Microsoft

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.

Super User

@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]))

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

## Helpful resources

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

#### Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors