In this blog post, I am going to show you how to create a drill down bar chart using parameter actions. An interactive drill down bar chart is a great way to show sub-categories, while still keeping the dashboard nice and tidy. Follow the steps below to learn how to create it.
Step 1. Create the bar chart view without any actions
It is always a good idea to create the “basic view” of how the chart is going to look before creating parameters, calculations or adding any actions. In this case, drag Category on to rows and Sales on to columns to create the basic bar chart that shows Sales by Category. Notice that Category is part of the Product Hierarchy together with Sub-Category, Manufacturer and Product Name. By clicking on the ‘+ sign’ on the Category pill the bar chart will show the sales for each Sub-Category.
Step 2. Create the parameter
The next step is to create the parameter values that we will later use to control the visualization. Create a value for each category and a reset value as per the screenshot below. Make sure the spelling is correct as the parameter action will not work if not. The reset value will be used to reset the view back to the original.
Step 3. Create the calculation with the parameter
You can see the “drill down calculation” I created in the screenshot below, with the Select Category parameter we created in the last step in purple. We only want the user-selected category to split into sub-categories, while the other categories stay at the category level. When the parameter is set to for example Furniture, the sub-categories of Furniture will be displayed, while the other categories will stay the same. When the parameter is set to Reset, only the category level will be shown as there is no Category named Reset.
Step 4. Test the calculation and parameters with the parameter control
Drag the drill down calculation “Selected Category” you just created onto the Color and Label tiles and Sales onto the Label tile as well. Right-click on the “Select Category” Parameter and select “Show Parameter Control”. Test that the drill-down calculation is working by selecting the different categories from the Parameter Control. When a category is selected, the respective bar in the bar chart should change to show the sales by sub-category. When ‘Reset’ is selected, all the bars should change back to the show the Sales by Category. Check out the GIF below for reference.
Step 5. Create the reset button
Before we add any Parameter Actions we want to create a reset-button that we can click on to reset the view. Create a new sheet and create a calculated field with the string “Reset” as shown below. Drag the calculated field on to the Label tile and change the mark type to ‘shape’. Change the fit to ‘Entire View’, the text alignment to ‘Centre’ and increase the text size. Also, don’t forget to disable the tooltip as you don’t want anything to pop up when you hover over the button. Please have a look at the GIF below to see all the steps.
Step 6. Create the dashboard
Create a new dashboard and add the bar chart and the reset button to the dashboard as shown in the screenshot below. If you want to make it look nicer and cleaner, feel free to remove headers, axis and do some basic formatting.
Step 7. Create the parameter actions
It’s finally time to make the bar chart interactive by adding parameter actions to the dashboard. We need to create two parameter actions, one for the bar chart and one for the reset-button.
Click on ‘Dashboard’ from the top menu and select ‘Actions’ from the drop-down.
Click on ‘Add Action’ and select ‘Change Parameter’ to create the first parameter action.
Add a Parameter Action for the bar chart with the selection as shown in the screenshot below.
Repeat the previous steps and add a Parameter Action for the Reset-button with the same selection as per the screenshot below. Both of the parameter actions have Select Category as Target Parameter, but the Source Sheet and Field are different.
You should now have two Parameter Actions as shown in the screenshot below. Click ‘OK’ and test out the actions.
Step 8. Enjoy your beautiful and interactive drill down bar chart!