cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rauniyara Frequent Visitor
Frequent Visitor

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

 

Power BI.png

1 ACCEPTED SOLUTION

Accepted Solutions
Thejeswar Senior Member
Senior Member

Re: Top 5 Values filter by Legend

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

figure.PNGTop 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 10
marclelijveld Regular Visitor
Regular Visitor

Re: Top 5 Values filter by Legend

Hi @rauniyara

You have to create a DAX measure for that. Try this: http://geekswithblogs.net/darrengosbell/archive/2016/06/04/179446.aspx 

- Marc

Re: Top 5 Values filter by Legend

If you drag the category over into the values field then there should be a visual level filter beneath that to select "Top N"

 

ss1.PNG

marclelijveld Regular Visitor
Regular Visitor

Re: Top 5 Values filter by Legend

The visual level filter to create a TOP N will work as well. But this wo’t create an ‘Other’ catergory.

- Marc
Highlighted

Re: Top 5 Values filter by Legend

Oh, 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. 

marclelijveld Regular Visitor
Regular Visitor

Re: Top 5 Values filter by Legend

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

Re: Top 5 Values filter by Legend

That is true. You cannot place a TopN filter and then also use a basic filter (one would overlap the other).  DAX will be the only option. Before you go there, I would get all categories in one columnto simplify things.  

marclelijveld Regular Visitor
Regular Visitor

Re: Top 5 Values filter by Legend

Look at the screenshot in the original post. The column is alrea there and is used as legend now. I don’t see any reason at all to create an additional column...

- Marc
Super User
Super User

Re: Top 5 Values filter by Legend

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?

Thejeswar Senior Member
Senior Member

Re: Top 5 Values filter by Legend

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

figure.PNGTop 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!!!