In this blog I will show you how to create a drill-down bar chart using Category and Sub-Category from sample superstore. These charts can be useful when looking at data that belong to a hierarchy, starting at the top of the hierarchy and then drilling down into the more specific sections. They also save space on the dashboard as the bar chart transforms with the use of the drill down.
Step 1
Drag sales onto the column shelf, category onto the rows shelf, and to also drag category onto colour on the marks card to colour our bars.
Step 2
We need to create a category set which will be responsible for the drill-down. To do this, right click on category in the data pane and go to create > set…
Make sure you are on the “select from list” option but don’t select any of the categories. Name this Category Set and click ok.
Step 3
Now we need to create a calculated field which will complete the drill-down process. Click on the drop down arrow in the data pain and select “Create Calculated Field”, name this field Sub-Category Drill and enter the equation “If [Category Set] then [Sub-Category] end”. This equation means that when I select a category on the bar chart, it will drill-down to that category’s specific sub-categories.
Step 4
Drag the Sub-Category Drill onto the rows shelf and we now have one step left to do…turn on the drill action. Go to Worksheet > Actions > Add Action. Name the action Drill-Down, select your worksheet from the source sheet drop down and select the Category Set from the target set drop down. Select “Assign values to set” for the bottom left section, and “Remove all values from set” for the bottom right section and click ok…
…and we are done! Now select any of the categories on the bar chart to see the following sub-categories in the drill down.
