Forum Discussion
Top 5 Values filter by Legend
Hi,
I have a Stacked Column Chart with Spend vs Fiscal Year data. The Total Spend columns are broken down into Material Category spend values through the Legend field.
How do I make it so that only the top 5 Material Categories by Spend are shown in detail within the columns and all other categories are grouped together as "Other" in the Legend? I can get just the TOP N Material Categories by Spend on the chart, but this will not include the "Other" Group consisting of all other categories.
I appreciate any help! Thank you!
Avi
Hi rauniyara,
Try doing the following steps to get the chart as per your need
1. Generate the Rank based on Revenue grouped by Year in a Calculated Column
2. Create another Calculated column which should give product name only for Top 5 Products by Revenue and for other products as "Others"
The Follwing is the DAX used
Rank = RANKX(FILTER('Top5Filter', 'Top5Filter'[Year] = EARLIER('Top5Filter'[Year])), 'Top5Filter'[Revenue],,DESC,Dense)This Rank is completely dynamic. Suppose a new value is going to be added in the future, this Rank will change accordingly
Top Product = IF('Top5Filter'[Rank] <= 5, 'Top5Filter'[Product], "Others")The Below is the screenshot after applying these steps
Top 5 Products displayed separately. All the other products are grouped as "Others". If any year has other product which is not present in any of the other year, then that will be displayed only for that particular year. In such case, the legend will have 6 values. So you should hide the legend in that case
Here is the link for the PBIX file for your reference
Hope this is what you need!!!
10 Replies
Hi rauniyara,
You have to create a DAX measure for that. Try this: http://geekswithblogs.net/darrengosbell/archive/2016/06/04/179446.aspx
- MarcOh, you are right. I misred that. Okay, this is what I would do. If it's the best practice, I don't know.
I would add a column with an "if" statement to get the names to what you want. If materials1 then materials1, if materials2, then materials2 and so forth, then use the else to group everything else into "other". Use that column as your categories.
- But then you’re hard-coding which materials are in your top 5. Possibly your top 5 is different if you select an other time frame. So your Top 5 have to be calculated dynamically. In that case, your only option is to use the visual level filter, but that doesn’t have the other category, or you have to calculate it in DAX like I suggested above.
- Marc
Hi rauniyara,
Try doing the following steps to get the chart as per your need
1. Generate the Rank based on Revenue grouped by Year in a Calculated Column
2. Create another Calculated column which should give product name only for Top 5 Products by Revenue and for other products as "Others"
The Follwing is the DAX used
Rank = RANKX(FILTER('Top5Filter', 'Top5Filter'[Year] = EARLIER('Top5Filter'[Year])), 'Top5Filter'[Revenue],,DESC,Dense)This Rank is completely dynamic. Suppose a new value is going to be added in the future, this Rank will change accordingly
Top Product = IF('Top5Filter'[Rank] <= 5, 'Top5Filter'[Product], "Others")The Below is the screenshot after applying these steps
Top 5 Products displayed separately. All the other products are grouped as "Others". If any year has other product which is not present in any of the other year, then that will be displayed only for that particular year. In such case, the legend will have 6 values. So you should hide the legend in that case
Here is the link for the PBIX file for your reference
Hope this is what you need!!!
If you drag the category over into the values field then there should be a visual level filter beneath that to select "Top N"
- The visual level filter to create a TOP N will work as well. But this wo’t create an ‘Other’ catergory.
- Marc
Hi,
I am not sure of whether i can solve this problem completely. Nevertheless, I'd like to try. Could you share the link from where i can download the PBI file?
