cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Multiple top N filters

Hi,

 

I am new to Power BI, but love it already! Amazing tool.

Unfortunately I am facing an issue where I am unable to add multiple top N filters to a bar chart.

 

What I am trying to visualize is the top 5 suppliers (based on spend) for the top 10 categories (based on spend) in a certain period. If I add the supplier dimension it messes up the chart, as some suppliers contain a large amount of suppliers.

 

Is there any way around this?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Multiple top N filters

Hi @Rob1992 ,

 

We can create a calculate column and use filter on this visual to meet your requirement.

 

1. We need to create a column to calculate the total of each category.

 

Sum = 
CALCULATE(SUM('Table'[Spend]),FILTER(ALLSELECTED('Table'),EARLIER('Table'[Category])='Table'[Category]))

 

Mu1.jpg

 

2. Then we can create a rank column based on sum column.

 

Rank = RANKX('Table','Table'[Sum],,DESC,Dense)

 

Mu3.jpg

 

3. At last we can put the rank column in filter on this visual and configure the top five of supplier name.

 

Mu2.jpg

 

Mu4.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

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

View solution in original post

6 REPLIES 6
Highlighted
Super User IX
Super User IX

Re: Multiple top N filters

@Rob1992 , refer if this can help

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User II
Super User II

Re: Multiple top N filters

It sounds like you're saying.... if i find the top 10 categories (based on spend) then i would like to show the top 5 suppliers...in those categories

You should be able to write a measure that can be applied to the supplier table that looks up the top ten categories, and then checks to see if the current supplier is in the top 5 spenders for those categories.... which returns a flag value, true/false that tells whether the supplier should be included in the report.

If you'd like help with the dax please post a sample table (from excel) of suppliers, and of spending and I'll help you with the measure.





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Highlighted
New Member

Re: Multiple top N filters

Hi, thanks for the quick response. You are correct, that is what I am trying to do.

 

Again, I am completely new to PBI so writing measures is new too.

 

I have anonymized the supplier names and categories in the sample below.

 

Supplier - NameCategorySpend
ChrisA2,28
MarkB8,43
ChrisB13,58
ChrisC2,61
ChrisC2,81
ChrisB14,38
ChrisD94,98
ChrisE9,77
ChrisF4,63
ChrisE12,9
ChrisD67,42
ChrisF9,03
ChrisE19,87
ChrisF15,91
ChrisC24,86
ChrisB4,78
ChrisF21,51
ChrisE12,86
ChrisE28,8
ChrisB14,82
ChrisC2,06
PeterG10,64
TedG7,8
TedH2,02
TedG10,55
TedH1,96
TedH0,96
MarkH27,6
MarkI53,7
MarkH20,28
MarkI9,5
MarkI5,46
MarkB16,86
MarkE9,39
MarkE11,61
MarkE9,24
MarkB16,5
MarkB6,84
MarkH2,94
MarkB10,52
MarkB56,25
MarkI10,32
MarkH27,72
MarkI20,79
MarkI17,04
MarkB4,23
MarkB11,28
MarkB11,28
MarkB8,51
MarkH4,14
MarkB9,24
MarkB16,06
MarkI11,91
MarkG12,03
MarkB7,6
MarkI20,09
MarkI20,84
MarkG22,52
MarkG25,96
MarkG22,76
MarkH4,14
MarkB7,52
MarkJ16,98
MarkH16,49
MarkB6,47
MarkG12,51
MarkB11,06
MarkI5,58
MarkI39,29
MarkG16,92
MarkG21,48
MarkH4,86
MarkC8,24
MarkG10,2
MarkG31,33
MarkH17,82
MarkH2,98
MarkH7,86
MarkI19,05
MarkI18,55
MarkB27,62
MarkB21,07
MarkB22,12
MarkA37,36
MarkC9,76
MarkE14,4
MarkC12,66
MarkC23,15
MarkD17,7
MarkC14,71
MarkK26,72
MarkK75,32
MarkC6,39
MarkC12,54
MarkC9,81
MarkL29,4
MarkC14,37
MarkD14,72
MarkD14,72
Highlighted
Super User II
Super User II

Re: Multiple top N filters

Working out a solution to this involved multiple, interlocked steps, so a made a little video to explain them. https://youtu.be/nJrbc6P209o  Let me know if you have any further questions.





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Highlighted
Community Support
Community Support

Re: Multiple top N filters

Hi @Rob1992 ,

 

We can create a calculate column and use filter on this visual to meet your requirement.

 

1. We need to create a column to calculate the total of each category.

 

Sum = 
CALCULATE(SUM('Table'[Spend]),FILTER(ALLSELECTED('Table'),EARLIER('Table'[Category])='Table'[Category]))

 

Mu1.jpg

 

2. Then we can create a rank column based on sum column.

 

Rank = RANKX('Table','Table'[Sum],,DESC,Dense)

 

Mu3.jpg

 

3. At last we can put the rank column in filter on this visual and configure the top five of supplier name.

 

Mu2.jpg

 

Mu4.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

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

View solution in original post

Highlighted
Community Support
Community Support

Re: Multiple top N filters

Hi @Rob1992 ,

 

How about the result after you follow the suggestions mentioned in my original post?
Could you please provide more details or expected result about it If it doesn't meet your requirement?

 

Best regards,

 

Community Support Team _ zhenbw

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

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors