In this article, we will show you, How to do the Tableau Rank Calculation with an example. For this, we are going to use the Employee table present the SQL Server. I suggest you to refer Connecting Tableau to SQL Server article to understand the configurations.
The following screenshot will show you the data inside the Table, and as you can see it has 14 records:
Before we get into the example, let me show you the list of available ranking options in tableau.
| Available Options | Result |
|---|---|
| Competition (1, 2, 2, 4) | This option will assign the identical rank to Identical values and skips the next rank. For example, if we have 100, 200, 300, 300, 400 then this option will assign ranks as 1, 2, 3, 3, 5 |
| Modified Competition (1, 3, 3, 4) | Modified Competition option will assign the identical rank to duplicate values. The Highest value will be ranked as 1, and the next two identical values will be ranked as 3. The next value will be ranked as 4. For example, if we have 100, 200, 300, 300, 400 then this option will assign ranks as 1, 2, 4, 4, 5 |
| Dense (1, 2, 2, 3) | Dense option will assign the identical rank to Identical values but it will not skips the next rank. For example, if we have 100, 200, 300, 300, 400 then this option will assign ranks as 1, 2, 3, 3, 4 |
| Unique (1, 2, 3, 4) | This option will allocate unique values to each and every record. Though there are identical values, it will assign a unique ranking. Here, the ranking factor will be decided by the Order we specify. |
Ascending and Descending Order Option: Under the Rank Calculation Type we have two options:
- Ascending: Rank will be assigned starting from lowest to highest. For example, if we have 100, 200, 300, 300, 400 then it will assign ranks as 1, 2, 3, 3, 5
- Descending: Rank will be assigned starting from highest to lowest. For example, if we have 100, 200, 300, 300, 400 then it will assign ranks as 4, 3, 2, 2, 1
Tableau Rank Calculation Basic
In this example, we will show you the basic approach to calculate the tableau Rank. For this, we are going to Drag and Drop the Occupation, Last Name, First Name from Dimensions Region to Rows Shelf, and Yearly Income from Measures region to Text filed present in the Marks Shelf.
In order to assign the Ranks in tableau we have to add the table calculation. To do so, please click on the Down arrow beside the Yearly Income measure (change as per your requirement) will open the context menu.
From the list of available options, either you can select the Add Table Calculation option or Quick Table Calculation option. For now, we are selecting the Quick Table Calculation option, and then Rank option
Once you select the Rank option, tableau will assign the ranks for each record based on the Yearly Income values
I think it will be nice to show the yearly Income values so that you can compare the Rank value with the yearly Income. Let us add the Yearly Income to Columns Shelf. As you can see it will create the Horizontal Bar chart. Please use the Show Me window to change the bar chart to Text as we show below.
The following screenshot will show you a better view.
Alter the Tableau Rank Calculation
To edit the existing table calculation, please click on the down arrow beside the table calculation will open the context menu. Here you have to select the Edit Table Calculation option as shown below.
Once you click on the Edit Table Calculation option, a new window called Table Calculation will be opened as we shown below. As you can see, tableau has selected the Rank Calculation Type as Competition (1, 2, 2, 4), and Compute using option as Specified Dimensions (Occupation, Last name, and First Name) by default. Here, you can uncheck the unwanted dimensions as well.
Let us change the Rank calculation type from Competition to Modified Competition (1, 3, 3, 4), and you can see the change in Rank
Let us change the Rank calculation type from Modified Competition (1, 3, 3, 4) to Dense (1, 2, 2, 3) and you can see the change in Rank
Lastly, we changed the Rank calculation type from Dense (1, 2, 2, 3) to Unique (1, 2, 3, 4). And you can see the change in Rank
Tableau Rank Calculation for Cross-Tab reports
In this example, we will show you, How to calculate the rank in tableau for the cross tabled reports (OR) calculate the rank for each partition. For this, we removed the First name from Rows Shelf and changed the Compute Using option to the pane (down then across). Here we don’t have any cross tabs so, it will calculate by going down.
First, we changed the Rank Calculation type to Competition (1, 2, 2, 4), and you can see the result. It is calculating the Rank for each Partition (here, it is Occupation)
Let us change the Rank calculation type from Competition to Modified Competition (1, 3, 3, 4). From the below screenshot you can see the Modified Competition Rank partitioned by occupation
Let us change the Rank calculation type from Modified Competition (1, 3, 3, 4) to Dense (1, 2, 2, 3). Form the below screenshot you can see the Dense Rank partitioned by occupation
Lastly, we changed the Rank calculation type from Dense (1, 2, 2, 3) to Unique (1, 2, 3, 4). You can see the Unique Rank partitioned by occupation
Tableau Rank Calculation – Second Approach
Please click on the down arrow beside the measured value (Here it is Yearly Income) will open the context menu. Here you have to select the Add Table Calculation option as shown below.
Once you click on the Add Table Calculation option, a new window called Table Calculation will be opened.
Please change the calculation type from Difference From option to Rank
Next, change the Compute Using option from a table (down) to Pane (across then down)
Thank You for Visiting Our Blog