Hi All,
I have Year & category hierarchy in Axis and one measure on Value. I have also have top level hierarchy in Legend.
Report
When 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
Solved! Go to 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.
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
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.
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:
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/
SOrry we cant do like this, bec we cant set Staic X Axis inhierarchy level.
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
User | Count |
---|---|
212 | |
53 | |
45 | |
41 | |
40 |
User | Count |
---|---|
270 | |
210 | |
72 | |
70 | |
65 |