Forum Discussion
Dynamic Top N legend based on field parameter selection
Hi,
I have a stacked column chart which looks at a metric dependent on the field parameter selection (metric x y & z), the same chart is also has a legend which is also determined by a field parameter selection (dimension a, b, c ).
When my users select to change what the chart is sliced by or the metric that it is looking at, I would like to show only the top 10 results e.g. top 10 of what the legend is/bars are sliced by.
Here is a more visual example,
The top black box is the selected field parameter that shows the metric on the Y axis whereas the 2nd black box is the selected field parameter that the bars should be sliced by. How can I limit the slicing such that only the top 5/10 are shown for each week.
I'm able to do this when it's a fixed legend by using nested if statements to scan the field parameter selection when it's a metric, but unable to figure out how to do this with a dynamic legend since the filter options on a widget require a dimension.
Thanks
4 Replies
Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi, here is a sample data set I've created to use as an example:
I can't attach files so here it is in table format
Week Country Continent Sub Region Clicks Cost Impressions 07/11/2022 England Europe Europe West 10 20 30 07/11/2022 Germany Europe Europe West 15 35 35 07/11/2022 Denmark Europe Scandinavia 11 21 31 14/11/2022 England Europe Europe West 9 19 29 14/11/2022 Germany Europe Europe West 5 6 10 14/11/2022 Denmark Europe Scandinavia 30 20 35 So with this data set, I have 2 field parameters set up as shown in this screenshot, one to filter by what the bar is sliced by and one to filter the metric it shows:
and the widget config:
How can I configure it such that I can see only the Top N countries or Top N whatever option is selected in the bar slicer. In this example, I have sumcost selected and the bars are sliced by the Country. How can I limit it so I can only show the Top N per bar. e.g. limit it to Top 2 so only Germany and Denmark are shown for 07 Nov and then Denmark & England for 13 Nov. And likewise, if I had sumimp selected and continent selected, how could I show the top 2 continents per week by sumimp.
Hopefully that makes it clear what the goal is.
TopN works on visual level, it doesn't go down to column level. For that you need to create a measure filter based on RANKX or your own handcrafted code.
