cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Help on drill down with hierarchy in chart

Hi All,

 

I have Year & category hierarchy in Axis and one measure on Value. I have also have top level hierarchy in Legend. 

ReportReportWhen drill downWhen drill down

My requirement is to have year common on axis while doing drill-down. So when we drill down to category, it always show year on axis and category on stacked bar and when we drill down to sub-category then sub-category on stacked bar and year on axis, etc...So year should be constant on x-axis.

 

I tried many options but didn't work. Any help on this highly appreciated. 

 

Regards,

Niket Talati

 

1 ACCEPTED SOLUTION
Eric_Zhang
Microsoft
Microsoft

As far as I know, it is not possible in powbi.

Try to use 4 stacked column charts, setting the year as Axis and using different levels as Legends.

View solution in original post

5 REPLIES 5
vivekr316
Regular Visitor

While this is not directly possible in Power BI, there is a simple workaround to achieve this.

 

Steps
1. Create a new table with the hierarchy columns in normalized form

ie. if your dimension data is as follows (Original_Table)

CategorySub-Category
C1SC1
C1SC2
C1SC3
C2SC4
C2SC5

 

create a new table with the data normalized to the following format (Normalized_Table)

CategorySub-CategoryLevel
C1SC11
C1SC21
C1SC31
C2SC41
C2SC51
SC1SC12
SC2SC22
SC3SC32
SC4SC42
SC5SC52


One way of achieving this on SQL is - 

 

select * into Normalized_Table from
(
select
distinct
Category,
Sub_Category,
1 as Level
from Original_Table

union

select
distinct
Sub_Category as Category,
Sub_Category,
2 as Level
from Normalized_Table
) Original_Table;

 

2. Create a bi-directional relationship from the Normalized_Table[Sub_Category] referring to Original_Table[Sub_Category]

 

3. Now add Normalized_Table[Level] as a slicer set to single selection and defaulted to 1

 

4. Create the stacked bar chart (or any other visual) with Normalized_Table[Category] as the legend

Now when '1' is selected in the slicer, the stack displays at Category level and when '2' is selected in the slicer, the stack displays at sub-category level

 

Vivek

Eric_Zhang
Microsoft
Microsoft

As far as I know, it is not possible in powbi.

Try to use 4 stacked column charts, setting the year as Axis and using different levels as Legends.

asocorro
Skilled Sharer
Skilled Sharer

While you can't keep Year fixed on the axis, you could create a measure that tells the current year selected and display it on a card.  Check out this post for a way of doing this:

 

 

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro
asocorro
Skilled Sharer
Skilled Sharer

While you can't keep Year fixed on the axis, you could create a measure that tells the current year selected and display it on a card.  Check out this post for a way of doing this:

 

https://blog.crossjoin.co.uk/2016/04/25/dynamic-chart-titles-in-power-bi/

 

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro
Baskar
Resident Rockstar
Resident Rockstar

SOrry we cant do like this, bec we cant set Staic X Axis inhierarchy level.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.