Welcome back to the “Power to the user” series of posts about parameters in Tableau.
In this blog post I will write about dynamic “top n” and dynamic sorting with parameters.
First thing, understand when & why you want to do something like this
In this was the end user will decide how many categories/ items to display in the view and also if you have multiple measures involved how to sort based on the favourite measure.
An example of application below.
DYNAMIC TOP N AND SORTING IN BAR CHART
This example uses Sample Superstore data from Tableau.
- I want to build a bar chart with dynamic top N based on Product Name and sorting based on
Also I want the Top N to update on the selected metric so it will show the top N of product based on the metric of the sorting.
Top N parameter:
2. First, build the view as below, dragging Product Name into Rows, Sales, Quantity and Profit into Columns as in the image below
3. Right click on Product Name in columns and select the filter option, then go in Top and select your favourite field to compute the top N on, then click on the dropdown and select the option “create a new parameter” as below
4. Configure the parameter “Top N” as you prefer, I made it showing by default 10 results, with minimum range 5 and maximum 200 and step size 5
5. Click ok. Now the user will be able to display a dynamic top N using the parameter in the top right corner of the worksheet based on sales:
Sorting by parameter (and connecting the top N to the measure you are sorting on):
6. As we want to be able to sort base on sales, quantity and profit, we need to go ahead and create a parameter
7. The parameter “sort by” will appear in the bottom left of the view as in the image below, just right click on it and select “Show parameter control” so you will have it in displayed in the top right corner of the worksheet:
8. Now we need to enable the parameter because now it is doing nothing! Create a calculated field which says:
case [Sort by]
when ‘Sales’ then sum([Sales])
when ‘Profit’ then SUM([Profit])
when ‘Quantity’ then SUM([Quantity])
9. Right click on Product Name which you are using in Rows and select “Sort”, then configure the sorting based on the new calculated field you just created and descending as below:
10. This is your view now:
11. One last thing to do, if you want also to link the Top N based on the measure you are taking into account with the sort by parameter, just go back in the filter (right click, edit filter, top) and select the calculation of the sort by. This will update the top N based on the metric of interest.
Hope you enjoyed!