Forum Discussion
drill down based on legends
I am creating a stacked area chart with Moths on X-axis and sales on Y axis and product as legends. Now I want to create a drill down on the charts with Praducts->Category->SubCategory, is it possible?
22 Replies
Hi powerbitv
It should be. Just add those fields to the Axis area of the visualisation panel as shown:
As mentioned I have Months on the x-axis..
IF you add these fields underneath "Months" in that field, then you can drill down to them.
Something like
I have a work around solution, not quite as elegant as drilling down the legend but workable.
For each level in the hierarchy create a Filter, then for each filter level create a chart and use the filter level as the legend.
e.g. if the levels are Product Category and Product Sub Category, then create two filters, one for Product Category and the other for Product Sub Category
Then for the charts, chart 1 had Product Category on the legend and Chart 2 had Product Sub Category as its legend.
Anonymous
That's the whole point, you want to avoid to have to use 2 charts..
For me it's not about avoiding two charts but reducing clutter in the 2nd level chart.
e.g. Assume Product Category contains 2 categories of vehicles, 4 wheeled and 2 wheeled, and the Product Sub Category contains for 4 wheeled vehicles trucks, cars and vans and for the 2 wheeled vehicles scooters, sports bikes, cruisers and dirt bikes.
Selecting the 2 wheeled vehicles on the Product Category filter dynamically updates the Product Sub Category filter and chart to only show the scooters, sports bikes, cruisers and dirt bikes without the clutter of the 4 wheeled vehicles.
Like I said before it's an alternative solution. I would still prefer to drill down on the legend, but until then this works for me.
If you want just one chart, another solution is to use a dynamic switch measure, where the filter dynamically changes the measure that is displayed on a single chart. Basically one creates measures for each item to be filtered in the 1st and 2nd levels and then one uses the filter to select which of those measures is displayed in the chart.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Measures-Titles-Using-SWITCH/td-p/200718
https://visualbi.com/blogs/microsoft/powerbi/dynamic-measure-selection-power-bi/
