Help on drill down with hierarchy in chart

[talatiniket]

Hi All,

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

ReportWhen 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

[Baskar]

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

[asocorro]

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/

[asocorro]

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:

[Eric_Zhang · Accepted solution]

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]

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)

Category	Sub-Category
C1	SC1
C1	SC2
C1	SC3
C2	SC4
C2	SC5

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

Category	Sub-Category	Level
C1	SC1	1
C1	SC2	1
C1	SC3	1
C2	SC4	1
C2	SC5	1
SC1	SC1	2
SC2	SC2	2
SC3	SC3	2
SC4	SC4	2
SC5	SC5	2

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