Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
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.
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
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.
Help when you know. Ask when you don't!
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 |
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.
Help when you know. Ask when you don't!
@Rob1992 , refer if this can help
https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |