Tableau Rank

What is the Tableau Rank Function?

Tableau Rank function is an analytics function that finds the top N or bottom N values across the entire table or for a section of rows with similar data in Tableau. This function assigns a whole number value, called a rank or row number, starting with one either in an ascending or descending order to each row of the dataset. Rows with the same values as per the defined criteria are assigned with the same ranks. The rows with the same ranks are added while performing the Tableau rank calculation for the next set of rows. 

Tableau Rank function can be handy in performing various analyses involving ranking the data points in a specific order, identification of top or bottom performances, or assigning rows based on defined criteria.

Syntax

The syntax of the Tableau Rank function is highlighted below:

RANK()

This function returns the standard competition rank for the current row in the partition. The same rank is assigned to any identical values.

For example, the below syntax assigns ranks to the dataset based on the commission values in  descending order.

RANK (expression, [‘asc’|’desc’])

• Expression values can be aggregate functions that determine the basis of your ranking.
• The rank values are assigned a sorting order determined by Asc/desc.

How to Use Tableau Rank Function?

To create or add a reference line in Tableau, follow the steps below.

Step 1: Connect your dataset to the Tableau interface.

Step 2: Click on Analysis – Create Calculated Field. 

Step 3: On the calculated field window, specify the field name and click on the arrow. 

Step 4: On the search bar, type rank and double-click on the RANK function.

Step 5: Specify the logic for the RANK function.

Tableau will create a horizontal bar automatically.

Step 6: Change the visual from a horizontal bar to a text table.

Step 7: Drag the Customer Rank field to the Columns shelf. Now, you can see ranks assigned to the individual rows based on the total purchases.

Note: While Tableau rank is a table calculation, there are a few alternatives for performing Tableau rank without table calculation. For example, calculate rank based on size using the SIZE() function or conditional ranking using the IF ELSE statements. 

Variants

There are multiple variants of the Tableau rank function. These include:

#1 – RANK_DENSE Function

• This function allocates a whole number rank or row number starting with 1, either in an ascending or descending order to each row like a Rank function.
• For the rows with similar values as per the defined criteria, the same rank values are assigned to each of the rows.
• Unlike the Rank function, for Tableau rank calculation of the next rows, no rank values are skipped even if there are rows with the same ranks. 
• For example, the below function will assign ranks based on Commission values in a descending order RANK_DENSE (SUM([Commission]), [‘asc’|’desc’]). 

Note that even though both Lisa and Sarah have the same rank and are given the same commissions, the next rank starts from 2 instead of 3.

#2 – RANK_MODIFIED Function

• This function allocates a whole number rank or row number starting with 1 either in an ascending or descending order to each row.
• For the rows with similar values as per the defined criteria, the same rank values are assigned to each of the rows.
• However, unlike the RANK_DENSE function, for Tableau rank calculation of the next rows, rank values are skipped to the number of rows with the same ranks. 
• Tableau deploys the below calculation methodology for the calculation of modified rank values Rank Modified = Rank + (Rank + Number of duplicate rows – 1) where Rank refers to the rank value of the last instance
• For example, the Modified rank will assign ranks below.

RANK_MODIFIED (SUM([Commission]), [‘asc’|’desc’])

• For the 1st two rows, the same rank value is assigned, i.e., 1. For the next row, the modified rank is calculated as Rank Modified = 1 + (1+2-1) = 3

#3 – RANK_UNIQUE Function

• This function assigns a distinct or unique rank for the current row as per the specified partition.
• In case of a tie, the rows are assigned different ranks. Hence, all the rows will have a unique rank across the data field.
• You can define the choice of data sorting order, i.e., ascending or descending. However, if no choice is provided, descending order is applied by default.
• Note that this function does not consider null values to rank assignment logic, and hence, neither those rows are numbered nor included in Tableau rank calculation logic in your dataset.
• For example, Rank Unique assigns the ranks as below.

RANK_UNIQUE (SUM([Commission]), [‘asc’|’desc’])

#4 – RANK_PERCENTILE Function

• This function allocates a percentile rank starting with 0 with a maximum value of 1 either in an ascending or descending order to each row.
• Tableau calculates the Rank Percentile for each row of the dataset as per below Rank Percentile = (Rank – 1) /(Total Rows – 1) 
• If the Tableau rank calculation results in a tie, then the assigned rank value is rounded down.
• For example, the Rank Percentile values are calculated below.

RANK_PERCENTILE (SUM([Commission]), [‘asc’|’desc’])

Based on the sorting, Sarah is assigned with Rank Percentile as (1 – 1)/ (5 – 1) = 0

Similarly, Barb is assigned with Rank Percentile as = (3 – 1)/ (5 – 1) = 0.5

Charles rank percentile is calculated as = (4 – 1)/ (5 – 1) = 0.75

Examples

In this section, we will go through different examples demonstrating the Tableau Rank function.

Example #1

In this example, we will demo adding ranks to the various sports using the RANK function in Tableau using the Toughest Sports dataset which contains 60 sports across 10 categories of athletic skills, such as endurance, strength, power, speed, agility, flexibility, nerve, durability, hand-eye coordination, and analytic aptitude. 

To add ranks to the dataset using the Tableau Rank function, follow the instructions highlighted below:

Step 1: Connect with the Toughest Sports dataset to the Tableau interface.

Step 2: Navigate to the Analysis – Create Calculated Field.

Step 3: On the calculated field window, use the RANK function syntax to specify the logic for the assignment of rank values.

Step 4: Drag and drop the Sport field to the Rows shelf and Total measure to the Columns shelf. 

Step 5: Drag and drop the Overall Rank field to the Columns shelf. 

Step 6: Click on the Sort ascending order icon.

Now you can see the rank values for each of the rows are sorted in ascending order as shown below.

Example #2

In this example, we will demonstrate using the RANK_MODIFIED function in Tableau using the Population dataset. The Population dataset contains information about the population of countries around the world and includes additional information such as Country Code, Country Name, and Population. 

To use the RANK_MODIFIED function in Tableau, follow the below steps:

Step 1: Connect to the Population dataset in Tableau using the File Navigator.

Step 2: Right-click on the Data pane to select Create Calculated Field.

Step 3: Select the RANK function from the search box and double-click on the function.

Step 4: Specify the logic for the RANK function in the calculated field window.

Step 5: Repeat the same process to create a calculated field using the RANK_MODIFIED function.

Specify the logic for the RANK_MODIFIED function as shown below.

Step 6: Drag and drop the Country Name to the Rows shelf and Country Population, Rank, and Modified Rank fields to the Columns shelf.

As you can see Tableau has assigned different rank values under RANK and RANK_MODIFIED functions where there is a tie. While Modified rank values are not skipped in the event of a tie, Rank values are skipped in case of a tie.

Example #3

In this example, we will create a Tableau rank within group using the eCommerce_data_2 dataset. The dataset contains comprehensive data on customer transactions and demographic data.

To create a Tableau rank within group, follow the steps outlined below:

Step 1: Import your dataset into the Tableau interface using the File Navigator.

Step 2: Right-click on the Data pane and choose Create Calculated Field.

Step 3: Create a calculated field Customer Rank by Purchase using the RANK function syntax.

Specify the logic based on the purchase amount.

Step 4: Drag the Product Category to the Filter shelf and apply the filter criteria as shown below. Here we have excluded all the null values.

Step 5: Drag and drop the Product Category and Shipping Region to the Rows shelf and Purchase Amount and Customer Rank measure to the Columns shelf.

Step 6: Click on the arrow next to Customer Rank and choose Edit Table Calculation from the context menu.

Step 7: In the Table Calculation window, choose Specific Dimensions under Compute Using. Choose both the Product Category and Shipping Region fields.

Select Deepest for At the level. Specify the Restarting every as Product Category.

Now your Tableau visualization is ready for view. As you can see, we have performed Tableau rank within group where rank is calculated within the Product Category and Shipping region. The ranks are restarted from 1 for each product category value.

On a similar note, you can extend the above logic to assign Tableau rank by group using the Table calculation screen.

Important Things to Note

• Tableau Rank function assigns ranks depending on the sorting order specified.
• Some of the key features of the rank function are also dependent on setting up table partitions.
• The rank function doesn’t include any null or blank values for the Tableau rank calculation values.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the Tableau RANK, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to Tableau RANK. Here we learn how to use rank Function with syntax, variants, examples, points to remember and template. You can learn more from the following articles –

• Tableau Index
• Tableau Aggregate
• Tableau Lookup