Tips and Tricks – Parameter Controlled Filter for Your Top Values

Click to view on Tableau Public – Parameter Controlled Filter for Your Top Values

Often when viewing reports the user will want to be able to choose how many results they see rather than having that decision made for them by the report creator. There is no built-in filter for this because you need to filter by rank. To achieve this you can use a custom filter. I’ll explain below how to achieve this and I’ll use the Order data from the EU Superstore sample data for this example.

Step 1:

Open Tableau and connect to the EU Superstore Order data.

Step 2:

Create a new sheet and drop country onto the details pill and double click profit. You will now have a map with points on each of the 15 countries but what if you only wanted to see the top 3 most profitable countries?

Step 3:

Create a parameter called “TopX”, set the data type to integer and the current value to 3.

Step 4:

Now we need to rank the most profitable countries from most profitable to least profitable. We do this by right clicking on Profit in measures and then selecting Create > Calculated Field. Call the calculation “Rank” and enter the following calculation:

RANK(SUM([Profit]),‘desc’)

Before hitting OK select “Default Table Calculation” highlighted in blue above“OK”. Change “Compute using:” from “Automatic” to “Country” and then select OK and OK again.

Step 5:

Drop the newly created “Rank” measure onto label and you will see the values are now labelled from largest to smallest.

Step 6:

Create another calculated field called “RankFilter” and enter the following calculation:

IF [Rank] <= [TopX] THEN ‘Show’ ELSE ‘Hide’ END

Step 7:

Drag the newly created “RankFilter” measure into the filter card and tick “Show”. Make sure you leave “Hide” unticked. Select OK and you will now only see the top 3 most profitable countries.

Step 8:

Right click on the TopX parameter and click “show parameter control”. You will now see this parameter and you are able to change this value. Change it to 2 and you will notice that only the top 2 countries remain.

Step 9:

Format the map to look how you want and there you have it. You now have a parameter controlled filter which allows the user to see the TopX most profitable countries!

View the finished dashboard on tableau public here