cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
talatiniket
Helper III
Helper III

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.

View solution in original post

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
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors