I was recently given the challenge of allowing a user to customise the view of a Tableau chart in 3 different ways:
- Choosing the Measure to sort by;
- Choosing the Sort Order;
- Choosing the number of records to be sorted.
Although I had some knowledge of how to switch the axis measure in Tableau using a parameter, and how to use a parameter and Top N filter to dynamically change the number of records being shown, combining these concepts proved to be much harder than expected.
However, with some trial and error, and some help from colleagues, I managed to find a workable solution; the walkthrough below uses Sample Superstore data.
Choose a measure to sort by
- Create a new Parameter ('Sort By') with a list of possible String values; these values should correspond to the possible Measure Names that can be viewed in the chart:
2. Next, create a new Calculated Field ('Sort By Calc') with an IF THEN or CASE statement that tells Tableau what Measure field to return when different parameter values are selected. The fields will need to be aggregated the way they will appear in the chart:
Choose whether the Top or Bottom records are shown
3. Create a new Parameter ('Top or Bottom customers') with a list of integer values 1 or -1. Be sure to add Display text for the user to select from: 1 will be used for Top values, and -1 for the Bottom values.
4. Now create a Calculated Field ('Top/Bottom calc') that multiplies the Measure Values by 1 or -1, depending on the user selection. This will later help us display the Top or Bottom values by changing the Order of the sort: when the Measure Values are multiplied by -1, the largest possible value (e.g. $14,203) will become the smallest value (-$14,203).
Limit the number of records in the view
5. Next, we want to control the number of records in the view - in this example, the number of customers being shown in a bar chart. We can start by creating another Parameter ('Number of Customers in View'); this Parameter could allow All integer values, a List, or a Range, depending on your requirements and preference.
6. Now we can build another Calculated Field ('Index calc'), which we will be using as a filter to limit the number of customers shown. We use the INDEX() function to allocate a number to each row of the data, and then compare the row number to the value of the 'Number of Customers in View' parameter:
7. As an extra step to help quickly check the number of customers in view, I also created a Calculated Field 'Row Numbering' that just contained the INDEX().
Build and configure the view
For this simple bar chart, I looked only at 2022 data, so filtered the view to just that year. We can add 'Sort By Calc' onto columns, then the 'Row Numbering' calculation and the Customer Name field onto rows, and set the chart type to Bars. We should make sure all parameters are showing on the Sheet, so that it's easy to quickly test as we go along.
We can see in the screengrab above that the Sales values are being used - meaning the 'Sort By' parameter is working - but that we are sorted alphabetically, and there are more than 10 customers in the view.
8. To make sure everything is set up properly, we can now add a sort on Customer Name using the 'Top/Bottom' calculated field we made earlier. Set the order of this sort to 'Descending'; that way when we are looking at Bottom N values (e.g. all values multiplied by -1), the lowest values will appear at the top of the view, and when we are looking at Top N values, the highest values will appear at the top instead.
9. For the final step, add the 'Index calc' onto our filters and set it to 'True' only, and check that it is being computed with Customer Names ticked under 'Specific Dimensions':
The results
The finished worksheet will look like this when we select '10', 'Top' and 'Sales' in the parameters...
...and like this if we change the selection to '5', 'Bottom', 'Profit':
A finished Dashboard demo of this method can be seen on Tableau Public here.