Top 5 Values filter by Legend

[rauniyara]

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

[Thejeswar · Accepted solution]

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!!!

[rauniyara]

Hi @Thejeswar,

Thank you for looking into this! Using the Rank and Calculated column allows me to get what I was looking for.

Appreciate your help!

[Ashish_Mathur]

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?

[joshcomputer1]

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

[marclelijveld]

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

- Marc

[marclelijveld]

Hi @rauniyara, 

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

- Marc

[joshcomputer1]

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]

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

[joshcomputer1]

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]

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