cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
talatiniket Member
Member

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. 

2016-06-14_15-30-47.pngReport2016-06-14_15-31-15.pngWhen 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

Accepted Solutions
Moderator Eric_Zhang
Moderator

Re: Help on drill down with hierarchy in chart

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.

5 REPLIES 5
Super User
Super User

Re: Help on drill down with hierarchy in chart

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

asocorro Member
Member

Re: Help on drill down with hierarchy in chart

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
asocorro Member
Member

Re: Help on drill down with hierarchy in chart

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
Moderator Eric_Zhang
Moderator

Re: Help on drill down with hierarchy in chart

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.

vivekr316 Visitor
Visitor

Re: Help on drill down with hierarchy in chart

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