Limit Number of Categories Selected From Hierarchy Slicer

[Anonymous]

I have a hierarchy slicer that has 3 levels. The first has two categories, the second has two categories, and the third has up to a hundred categories depending on which combination of the first two levels are selected. My issue lies in my two main visualizations dependent on the slicer: a stacked column chart and line chart. The legend is dependent on the third level of the hierarchy since I would like the user to be able to compare values based on the most specific category. However, if the user selects more than 10 categories or the entirety of say one of the second level hierarchy options, there ends up being hundreds of lines on my line chart and hundreds of levels to my bar chart. Is there a way for me to have Power BI average values when the user selects >=10 categories without making the legend completely dependent on my second hierarchy level? 

[Anonymous]

These visuals don't look too attractive, do they... But the reason for making the legend dependent on the third hierarchy level is that it makes it extremely easy for the user to compare two or three specific levels but only if they select a small number of categories.

[v-xicai]

Hi @Anonymous ,

You may create column or measure to get the average, then choose the visual Line and stacked column chart to display the result, putting the new created column or measure onto Line values box.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi @v-xicai , I can get the average by making the legend in my visuals dependent on the second hierarchy level. However, I wanted a way to limit the number of categories that the user is able to select so the plot does not get too crowded when selecting from the third hierarchy level which contains a lot of categories. I figured that I'm just going to create two separate pages of my report -- one that allows the user to choose from the third hierarchy level (hopefully they will realize that the plot will get messy if they select > 10 categories) and one that allows the user to only choose from the first or second hierarchy so it just includes totals and averages.