Someone asked me earlier today, how can I rank a list of four items in Tableau if there are two items of the same value? I’ll talk about the answer below, but that prompted me to think about the common questions regarding ranks in Tableau and write this post.

I’ll go over the basic rank table calculation and the 4 other options that you have at your disposal when some values are of the same value.

The basic — Rank()

The first option someone will turn to when investigating how to rank their data. Tableau defines it here:
Assigns a whole number rank starting with 1, in ascending or descending order to each row. If rows have the same value, they share the rank that is assigned to the first instance of the value. The number of rows with the same rank is added when calculating the rank for the next row, so you may not get consecutive rank values.

Assigns a whole number rank starting with 1, in ascending or descending order to each row. If rows have the same value, they share the rank that is assigned to the first instance of the value. The number of rows with the same rank is added when calculating the rank for the next row, so you may not get consecutive rank values.

In this case, when rows share the same value, they will receive the same rank. The following rank number will be skipped. I’ll use the Sample — Superstore data to demonstrate.

In the above image, we can see that once we get to row 4, multiple people have ordered 34 times. With Rank(), they share number just after the previous rank i.e 4.

Something that’s important to note, is that null values will be ignored by all ranking functions.

What if I didn’t want to skip any rankings?

RANK_DENSE()

RANK_DENSE() returns a ranked list that gives identical values an identical rank, however no gaps are inserted into the ranks.

This will return the following:

Rank Dense Rankings

We can see that this means in the case of tied values, the current rank increments by 1, and the next rank will also be an increment of 1.

RANK_MODIFIED()

This rank function will return rank as a modified competition rank. This means that identical values will be given an identical rank, however the rank will be as if it were in a competition. E.g 1,3,3,4.

We can see an example below:

Rank Modified

RANK_UNIQUE()

Rank Unique, as the name suggests, will return a unique rank for every value. Identical values will also be assigned a different rank number. For the customer orders data set, the following result is displayed:

Rank Unique

RANK_PERCENTILE()

The final rank function that we’ll cover will be the rank percentile. What this rank function does, is normalise the values. This means that it will assign a rank between 0 and 1, and assigns the percentile rank of the value between the min and max values. See below the rank percentile function for the customer orders data.

Rank Percentile

Now you’ll have seen the five rank functions in Tableau, and will be able to pick and choose the appropriate rank function for your purpose. Happy visualising!

The Data School
Author: The Data School