Welcome to my tutorials on Tableau parameters. These tutorials will demonstrate step by step how to create 5 unique parameters to produce an interactive bar chart with dynamic measures. This type of visualisation is an exploratory style that allows users to interact with and investigate their desired measures and categories.
Part 1 will cover the following parameters:
- Parameter 1 – ‘Top N Sub-categories’ (integer parameter)
- Parameter 2 – ‘Top/Bottom’ (integer parameter)
Part 2 will cover the remaining parameters:
- Parameter 3 – Dynamic Measures – ‘Select a measure’ (string parameter)
- Parameter 4 – Dynamic filter – ‘Product position’ (string parameter)
- Parameter 5 – Reference Line Benchmark ‘Sales Benchmark’ (float parameter)
If you follow along these tutorials, you will create this interactive bar chart that will display the top or bottom N Sub-Categories by Sales. You can watch and listen to each section as you follow along or scroll down to the end of the page to watch the full video instructions.
This visualisation allows us to compare the product sub-categories by the product position dynamically; while also observing the total sales, the total sale volume and the top or bottom N Sub-Categories.
Additionally, it can be useful to save space as one chart can display two or more measures at a time.
You can follow along by downloading the dataset I used to create this chart, or you can try and apply this to your own dataset.
Getting Started - Adding the dataset to Tableau
Alright let’s dive in.
We will begin with loading the dataset into Tableau. Start by opening Tableau and connect to the data. It’s a Microsoft Excel .xlsx file so you can select that option. You will be taken to the Data Source page where you should automatically have the table sheet open. It is a cleaned dataset, so you don’t need to make any changes here; just go ahead and click on sheet 1.
Parameter 1 – Top N (an integer parameter)
Let us begin with a question we might be trying to solve. The question I am going to pose is: What are the Top N Sub-Categories by sales?
This question needs us to look at product sub-categories by sales. We can drag product sub-categories to the rows shelf; while, placing sales to the column shelf and leaving this as sum.
Following this, we need to sort it by clicking the ‘Sort by descending‘ button on the Toolbar, add some text labels by clicking the ‘T’ on the Toolbar and click the dropdown menu to select ‘Fit Height.’
Now, we want to find the Top N Sub-Categories while, at the same time, allowing the user to decide what number of sub-categories to show. To do this we need a parameter to create a dynamic filter. There are a few options to create a parameter; we will use one method here.
If you look at the search bar on the left panel, you will find a little drop down at the end.
Click this, then click ‘create parameter’.
- Re-name the parameter – call it, ‘Top N Sub-Categories‘.
- Change the data type to an integer.
- Click okay.
Next, we’ll start by right-clicking on the Top N Sub-Categories parameter and selecting ‘Show Parameter.’ We can experiment with the Top N Sub-Categories—selecting five, for example, to observe that nothing changes.
To ensure proper filtering, drag Product Sub-Categories onto the Filter’s Shelf, click ‘Use All’, then we will go to the ‘Top’ Filter option.
- Choose to filter ‘by Field’.
- Then in the dropdown, select the parameter ‘Top N Sub-Categories’.
- We are filtering based on the Sum of Sales.
Now, we can test our work. Try inputting 3, then 5, to confirm that everything runs smoothly. It works!
What did we just do? We’ve created our first parameter!
Parameter 2 – Top/Bottom (an integer parameter)
We now want to expand on this question and instead find, not only the Top N Sub-Categories by sales, but to also get the Bottom N. The user will then be able to use a top or bottom filter, so we can see the Top or Bottom N Sub-Categories by its sales.
Let’s begin again, say ‘create a parameter’, as we did before, and name it ‘Top/Bottom’. Moreover, we need to:
- Change the data type to an integer.
- Choose ‘list’ at ‘Allowable values’.
- Type 1 in the ‘values‘ column.
- Change the display to ‘Top’
- Type -1 in ‘value‘.
- Change the display for -1 to be ‘Bottom’.
- Click Ok.
Furthermore, we are going to say, ‘show parameter’. It will once again show up on the right filters pane; however, we want to change it to a single value list. So, by clicking on the parameter dropdown on the filter shelf we can change it to ‘Single value list’.
We can test it – it doesn’t work.
Parameters on its own do nothing – we need to use the parameters in other ways before they can behave the way we want them to.
Right. Let’s fix this.
We will go to the ‘Product Subcategory’ filter in the ‘Filters’ shelf and right click ‘Product Subcategory’ to see the menu.
- Choose to edit the filter.
- Go back to ‘Top’
- Instead of filter ‘by field’ choose ‘by formula’
- In the dropdown change to the parameter ‘Top N Sub-Categories’
- In the formula field enter this formula:
SUM([Sales]*[Top/Bottom])
So why did we need to do this type of calculation?
Let’s pause for a moment and enrich our understanding.
Tableau does not natively have a bottom filter; hence we need to create a parameter like this.
Recall, in the parameter that it was an integer parameter and therefore it returns 1 or -1. The reason this calculation is necessary is because now, when we are summing the sales, we need to inverse the sum of sales when the bottom filter is chosen or to keep the sum of sales as normal.
Take this for example: if we have 1, 4, 10 and 16 when we sort this to find the top 2 values, we would get 16 and 10. Now if we multiply this set of numbers by -1, we have the values -1, -4, -10 and -16, the top 2 values would be -1 and -4.
In essence, we are always filtering by the top sales.
After filling in the calculation we can just click ‘okay’ and then go to the ‘Top/Bottom’ parameter and select ‘Bottom’ and select ‘Top’ to see that both work then go back to ‘Bottom’ so we may rename the heading.
We are going to change the heading so that it will become a dynamic heading that changes depending on whether ‘Top’ or ‘Bottom’ is selected.
Right click into the Title, choose ‘edit title’. We are going to clear the title and add in our parameters.
First, we will add from the insert menu our ‘Top/Bottom’ parameter, and then next to that, we will insert the ‘Top N Sub-Categories’ parameter. We can write out next to the parameter ‘Sub-Categories by sales’ and hit ‘okay’.
Now we can see that we have a dynamic title where it says that it is the ‘Bottom 5 Sub-Categories by sales’ and if we change the parameter to ‘Top’ then we’ll see ‘Top 5 Sub-Categories by sales’ and if we change the ‘Top N Sub-Categories’ to 3 we see ‘Top 3 Sub-Categories by sales’.
It all works perfectly.
In the next section, we will add a new parameter to this chart to create a dynamic measures selector.
To end off, right click on your sheet at the bottom tab and choose ‘duplicate’ we are creating a copy of our sheet to use at a later date.
In Summary
We will conclude here for this tutorial and continue with building our interactive chart in the next tutorial.
Just to recap, we covered how to add a .xlsx dataset to tableau and we created 2 different parameters:
- Parameter 1 – ‘Top N Sub-categories’ (integer parameter)
- Parameter 2 – ‘Top/Bottom’ (integer parameter)
We also created a simple calculated field and a dynamic title to go with our parameters.
Parameters are powerful tools. They allow you the ability to create interactive dashboards or charts that lets users input their desired values and investigate their specific questions. You can create valuable titles that dynamically display your insights which can quickly capture the message you want to share with your viewer.
It transforms an ordinary bar chart into something more innovative, more efficient and more insightful by giving users the power of discovery– the power of interactivity.
You can now watch the full video below to recap everything covered above.
Reference to source of original data:
Data from Kaggle
