Forum Discussion

rauniyara's avatar
rauniyara
Regular Visitor
7 years ago
Solved

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 th...
Need Help
  • Thejeswar's avatar
    Thejeswar
    7 years ago

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

joshcomputer1's avatar
joshcomputer1
Helper V
7 years ago

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's avatar
marclelijveld
Resolver I
7 years ago
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's avatar
    joshcomputer1
    Helper V
    7 years ago

    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's avatar
      marclelijveld
      Resolver I
      7 years ago
      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