cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 ?

TopN_help.PNG

Thanks a lot

2 REPLIES 2
isabella
Microsoft
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

isabella_0-1653896369399.png

 

group by reason

isabella_1-1653896369399.png

 

isabella_2-1653896369400.png

 

sort count

isabella_3-1653896369400.png

 

 

Now the data looks as:

isabella_4-1653896369401.png

 

We can delete the bottom 2 row

isabella_5-1653896369401.png

 

 

isabella_6-1653896369402.png

 

 

isabella_7-1653896369402.png

 

Now the table looks as:

isabella_8-1653896369402.png

 

 

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

isabella_9-1653896369404.png

 

 

isabella_10-1653896369405.png

 

remove the Count column

Then  group by reason and department

 

isabella_11-1653896369405.png

 

 

Then sort the reason

isabella_12-1653896369405.png

 

Then sort    the Count column

isabella_13-1653896369406.png

 

 

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 )

isabella_14-1653896369406.png

 

 

Sum the Count and index column

isabella_15-1653896369407.png

 

 

isabella_16-1653896369407.png

 

 

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)

 

isabella_17-1653896369408.png

 

 

In visualization

isabella_18-1653896369408.png

 

 

isabella_19-1653896369408.png

 

 

 

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
Super User
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
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

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

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

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

Power Platform Conf 2022 768x460.jpg

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