Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I have Year & category hierarchy in Axis and one measure on Value. I have also have top level hierarchy in Legend.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |