Have you been evaluating Looker for your organization? Read more to learn about this new analytical platform’s features and how it compares to a more established business intelligence tool.

In Tableau, ranks of dimension elements work properly when all the elements are selected. Once you change the view to filter on one or few selected elements, it starts ranking the list again instead of showing the original ranks.

Rank is part of a table calculation function and can be manipulated by adjusting related settings. In this post, we will examine how to tweak and combine such settings to retain the original ranking in a new filtered view.

We’ll consider the following example of ranking countries based on GDP per capita.

Background

The image below displays a table view of different countries’ GDP information. Here we have ranked countries based on GDP per capita.

In the above scenario, if we select only Australia as the country, the Tableau engine ranks it to No. 1 whereas its original rank is No. 15 (see screenshot below).

Note: Here we have used below formulas for GDP Rank which can be modified according to the requirement. The term ‘desc’ in the definition is used to sort values in descending order which can be changed to ascending order by setting it to ‘asc.’ Also, this result is computed across the table.

Now that we know the issue, here’s how we resolved it using a few simple steps:

Step 1: Create a parameter for Country. We will use this parameter to replace the Country filter.

Right click on the Country, go to Create > Parameter:

This window will appear:

Now let’s create another field called Country Lookup, and using the following formula:

Step 2: Drag the Country Lookup field in the filter pane and set it to default as True.

Step 3: Drag Country parameter to the filter pane and we are done! Select any country, it will display its original rank only.

Here, we have used a unique rank with a simple calculation which can be used with multiple levels of nested calculated fields. There are multiple ways to rank the data and Tableau provides five different rank functions listed below:

1. Rank (1,2,2,4)
2. Unique Rank (1,2,3,4)
3. Dense Rank (1,2,2,3)
4. Modified Rank (1,3,3,4)
5. Percentile Rank (100%, 75%, 75%, 25%)

Also, we can set ranks specific to table, pane cells or based on the dimensions. If it’s more than two dimensions, you can decide to restart the calculation at the required level. For example, we can have ranks based on cities of a country and it can be restarted at each country level.

Note: Nulls are ignored in ranking functions. They are not numbered and they do not count against the total number of records in percentile rank calculations.

Have Tableau questions? Reach out to us at info@aarete.com