Forum Discussion
Helper IHierarchy bar chart based on measures
Hi,
I have a bar chart with the months as my X-axis and 3 measures in Y-axis.
Meaure 1: Total cost consultants
Meaure 2: Total cost consultants from Company A
Meaure 3: Total cost consultants from Company B
Is there a way to have a 2 level hierarchy in the bar chart without having to select anything in a slicer, in the same way as you can drill down when you have a date hierachy ? Level 1 shows Total cost consultants, Level 2 will show Measure 2 & 3 as indiviudal columns
Hi gfdytr4 ,
Thank you for reaching out to the Microsoft fabric community forum. Also thank you danextian
As mentioned by rohit1991 , Power BI doesn’t natively support measure-level drill-down in bar/column charts.
The most reliable approach is using a Matrix visual, where you can place measure labels (with levels) on rows and time on columns, allowing drill-up/down natively.
I hope this is clear. If you have any further questions, please feel free to reach out.
Thank you.
6 Replies
rohit1991Super User
Hi gfdytr4
Yes, you can simulate a two-level hierarchy in a bar chart using measures by combining a disconnected table with a dynamic DAX measure. Power BI doesn’t support native drill-down between measures, but this approach replicates the behavior effectively.
First, create a disconnected table named "MeasureSelector" with two columns: one for level and one for measure labels. For example:
Level | Measure Name
1 | Total Consultants
2 | Company A Consultants
2 | Company B ConsultantsThen, create a new measure using SWITCH to return the appropriate value based on the selected measure name:
Selected Measure = SWITCH( SELECTEDVALUE('MeasureSelector'[Measure Name]), "Total Consultants", [Total Consultants], "Company A Consultants", [Company A Consultants], "Company B Consultants", [Company B Consultants] )In your bar chart:
-
Place Month on the X-axis
-
Place the Selected Measure on the Y-axis
-
Add MeasureSelector[Measure Name] to the Axis or Legend
-
Enable drill-down on the visual
Now you will see level 1 showing Total Consultants, and when you drill down, it will break into Company A and Company B consultants. This simulates a hierarchy-like drill experience using measures without the need for slicers.
gfdytr4This is what i want but placing Measure name in the legend returns all 3 columns. The drill up/down icons dont appear. There is no enable drill-down for clustered column chart that i can find.
Placing the measure name in Y-axis defaults to count and does not make the drill up icons apprear in the visual
- rohit1991
Hi gfdytr4
You're correct that clustered column charts in Power BI don't support drill-down when using a text field like "Measure Name" from a disconnected table in the legend. That’s why all three measures appear at once, and no drill icons are shown. To achieve your goal of a two-level hierarchy where level 1 shows Total Consultants and level 2 breaks it down into Company A and Company B ,the best and fully supported solution is to use a Matrix visual.
To resolve it, Create a disconnected table with your measure labels and their levels (e.g., Total Consultants = Level 1, Company A/B = Level 2). Then, create a SWITCH-based dynamic measure that returns the appropriate value based on the selected label. In the matrix, place Month on the columns, Measure Name on the rows, and your dynamic measure on values. This setup supports drill-up/down natively within the matrix, so the user can expand from total to breakdown without any slicer.
-
Hi gfdytr4 ,
Thank you for reaching out to the Microsoft fabric community forum. Also thank you danextian
As mentioned by rohit1991 , Power BI doesn’t natively support measure-level drill-down in bar/column charts.
The most reliable approach is using a Matrix visual, where you can place measure labels (with levels) on rows and time on columns, allowing drill-up/down natively.
I hope this is clear. If you have any further questions, please feel free to reach out.
Thank you.
Hi gfdytr4 ,
I wanted to follow up on our previous suggestions. We would like to hear back from you to ensure we can assist you further.
Thank you.
- danextian
If you mean to show a total bar then total for each individual company like below, you will need to use a disconnected table containing a column of company names and a row for the word total.
Please see the attached pbix.
