Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rauniyara
Regular 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
Thejeswar
Resident Rockstar
Resident Rockstar

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

View solution in original post

10 REPLIES 10
Thejeswar
Resident Rockstar
Resident Rockstar

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

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
Super User
Super User

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
joshcomputer1
Helper V
Helper V

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

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

- Marc

Hi @rauniyara

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

- Marc

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. 

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

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.  

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.