Use a dynamical regrouping as a legend

[JivanMansoor]

Hi,

I've plotted into a stacked column chart the number of fishing methods ("method") used per month and year in a particular zone and village. Here is what it looks like right now:

Now, I'd like to show the top 3 most used methods together with a fourth method, "autre". The latter should regroup all the methods that are not top 3 under a single label.

I've managed to this by adding a static, calculated column in my table, which uses the RANKX function. Now I have an additional column ("method_aggregated") that is equal to "method" for the top 3 methods used, and is valued "autre" otherwise:

Now I'd like to dynamically determine what falls into the "autre" bin each time I change my filters, but I'm stuck. Any ideas on how I can do this? Workarounds are also welcome.

Here is what I've already tried:

• Determining the category (top 3 or "autre") through a measure and using the measure as a legend --> didn't work, as you can't use measures as a legend in Power BI.
• Creating a supporting table with all the possible filter combinations and their corresponding category labelling --> didn't work, as it's impossible to include all the possible date intervals.

Any help will be really appreciated!

Thanks,

Mansoor

[v-dineshya]

Hi @JivanMansoor ,

Thank you for reaching out to the Microsoft Community Forum.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @JivanMansoor ,

We haven’t heard from you on the last response and was just checking back to see if your query got answered. Otherwise, will respond back with the more details and we will try to help.

Thank you.

[v-dineshya]

Hi @JivanMansoor ,

We haven’t heard from you on the last response and was just checking back to see if your query got answered. Otherwise, will respond back with the more details and we will try to help.

Thank you.

[v-nmadadi-msft]

Hi @JivanMansoor,

Thank you for reaching out to the Microsoft Community Forum.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[lbendlin · Accepted solution]

Filtering the top products alongside the other products in Power BI - SQLBI

Buckle up, it will be a wild ride.