The purpose of this blog is to show how to make a double drill down bar chart. I will use the usual Superstore dataset. In essence, I would like to be able to click on one of the category bars and see the subcategory bars and then click on one of the sub-category bars and see the merchants in this subcategory. Visually this is the journey I am aiming for:

To start with I create a simple bar chart showing the sales per category.

Step 1: Create two parameters.

One for the Categories and one for the Sub-categories and show both of the parameters in the view.

Step 2: Create two calculated fields - one for the category and one for the sub-category and drag them on the rows shelf.

Calculated field for the category:

if [Category]=[Parameters].[Category]
then [Sub-Category]
ELSE ''
END

Calculated field for the sub-categories:

if [Category]=[Parameters].[Category] and [Selected category]=[Sub-category]
then [Manufacturer]
ELSE ''
END

Step 3: Create an action for changing the category parameter

Select Worksheet from the top menu and click on Actions. A shortcut for this is Ctrl+Shift+A. Add an action and configure it this way:

Step 4: Create an action for changing the sub-category parameter.

In the source field in the action configuration I select Selected category.

And now we have a fully functioning double-drill bar chart.

Voila!