Forum Discussion
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?
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]))2. Then we can create a rank column based on sum column.
Rank = RANKX('Table','Table'[Sum],,DESC,Dense)3. At last we can put the rank column in filter on this visual and configure the top five of supplier name.
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
6 Replies
v-zhenbw-msftCommunity Support
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]))2. Then we can create a rank column based on sum column.
Rank = RANKX('Table','Table'[Sum],,DESC,Dense)3. At last we can put the rank column in filter on this visual and configure the top five of supplier name.
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
- amitchandak
Super User
Rob1992 , refer if this can help
https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/
- kentyler
Solution Sage
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.
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 - Name Category Spend Chris A 2,28 Mark B 8,43 Chris B 13,58 Chris C 2,61 Chris C 2,81 Chris B 14,38 Chris D 94,98 Chris E 9,77 Chris F 4,63 Chris E 12,9 Chris D 67,42 Chris F 9,03 Chris E 19,87 Chris F 15,91 Chris C 24,86 Chris B 4,78 Chris F 21,51 Chris E 12,86 Chris E 28,8 Chris B 14,82 Chris C 2,06 Peter G 10,64 Ted G 7,8 Ted H 2,02 Ted G 10,55 Ted H 1,96 Ted H 0,96 Mark H 27,6 Mark I 53,7 Mark H 20,28 Mark I 9,5 Mark I 5,46 Mark B 16,86 Mark E 9,39 Mark E 11,61 Mark E 9,24 Mark B 16,5 Mark B 6,84 Mark H 2,94 Mark B 10,52 Mark B 56,25 Mark I 10,32 Mark H 27,72 Mark I 20,79 Mark I 17,04 Mark B 4,23 Mark B 11,28 Mark B 11,28 Mark B 8,51 Mark H 4,14 Mark B 9,24 Mark B 16,06 Mark I 11,91 Mark G 12,03 Mark B 7,6 Mark I 20,09 Mark I 20,84 Mark G 22,52 Mark G 25,96 Mark G 22,76 Mark H 4,14 Mark B 7,52 Mark J 16,98 Mark H 16,49 Mark B 6,47 Mark G 12,51 Mark B 11,06 Mark I 5,58 Mark I 39,29 Mark G 16,92 Mark G 21,48 Mark H 4,86 Mark C 8,24 Mark G 10,2 Mark G 31,33 Mark H 17,82 Mark H 2,98 Mark H 7,86 Mark I 19,05 Mark I 18,55 Mark B 27,62 Mark B 21,07 Mark B 22,12 Mark A 37,36 Mark C 9,76 Mark E 14,4 Mark C 12,66 Mark C 23,15 Mark D 17,7 Mark C 14,71 Mark K 26,72 Mark K 75,32 Mark C 6,39 Mark C 12,54 Mark C 9,81 Mark L 29,4 Mark C 14,37 Mark D 14,72 Mark D 14,72 - kentyler
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.
- v-zhenbw-msft
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.
