cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
powerbitv
Frequent Visitor

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?

21 REPLIES 21
Kiwifruta
Frequent Visitor

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.

 

@Kiwifruta 

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/20...
https://visualbi.com/blogs/microsoft/powerbi/dynamic-measure-selection-power-bi/

@Kiwifruta 

Wouldn't suit 'Edit Interactions' better for what you're trying to do? Turn the first graph into a slicer for the second graph?

you are completely right!

Phil_Seamark
Microsoft
Microsoft

Hi @powerbitv

 

It should be.  Just add those fields to the Axis area of the visualisation panel as shown:

 

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

 

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

For Axis, I have it like this..

Months

ProductGroup

Product

Category

The deafult on the X-axis is Months, when I drill down, Months from the X-axis disappears, and it is replaced by ProductGroup->Product->Category. I want to retain the month on the x-axis while drilling down.

Sean
Community Champion
Community Champion

To achieve this you need to use the 3rd button from left to right in the Top left corner of the visual

The Expand all down one level in the hierarchy button

then the axis will display all

Good Luck! Smiley Happy

powerbitv
Frequent Visitor

"Expand all one level down in the hierachcy" is working file, but it has several item on the x-axis, Month-ProductName (eg: Jan-ProductName1, Jan-ProductName2... Feb-ProductName1, Feb-ProductName2.. and so on).

 

I need just Jan, Feb, March on x-axis and Porduct details "drilling down" and stacked area chart like below

Capture.JPG

@powerbitv 

 

No one gets it :). 

 

I created for this a second custom table with an unpivot on those 4 columns and a relationship with the ID between  them. Then 1 Chiclet slicer for the unpivotted table and 4 'classic slicers'. 

 

But yes, I'm quite curious if there is an easier way for my end users to get a drill down on legend. 

I would love this also, would be very powerful.

This is the closest workaround I could do so far (x-axis should be named legend, I know). But still, if you wanted to pin it for just 1 of the types, you have to click an option of a 'normal' slicer and then hit the subtype option. I've created something similar for the y-axis as well so it would be completely flexible. 

 

My problem so far is, it is fancy, but mainly used for data exploring to see how different section are related to each other. This is generally done by those who already know how Power BI works. And as soon as you find the consistency, you pin that consistency against the wall and the dynamic drill down is no longer required. 

 

Perhaps when I get to the sales numbers instead of KPI's, that this might become interesting. 

 

Changing legend 2.png

Changing legend 1.png

 

Can you walk through how you created this?

Like this:

 

Untitled.png

 

Then use 'Table 2'[Filter] for the column required in a filter visual. 

worked great, thanks

Top!

I'm just missing a real life application for this. The only really useful things I've found is if you want to mess around with the data as a user. 

It has great life application
I am facing the same issue with composite Index evolution over time
I wanna see evolution of composite index in Y and time in X, but i want to be able to drill down the subcategories of my composite index:
click on the graph it shows the evolution of the three main categories over time
click on one main categories it shoes me the evolution of the variable it is composed of (over time in X)
Value is the same (measure called value who does sort of an average)

Anonymous
Not applicable

Did you ever get a resolution on this, I would like to accomplish the same task.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.