In our previous article, we had shown you the basic approach to calculate rank in tableau. In this article we will show you, How to calculate Tableau Rank using the table calculation with 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. Following screenshot will show you the data inside the Table, and as you can see it has 14 records:
Tableau Rank Calculation – Advanced
In this example, we will show you the advanced approach to calculate the tableau Rank. For this, we are going to Drag and Drop the Occupation, Last Name, and First Name from Dimensions Region to Rows Shelf. Next, drag the 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 besides the Yearly Income measure (change as per your requirement), or right click on it will open the context menu. From the list of available options, please select the Create option and then Calculated Field… option as shown below.
Once you click on the Calculated Field… option, a new window will be opened to write the calculation.
RANK
In this example, we will calculate the rank so, we are using the RANK function. This function accepts two arguments first, aggregated measure (or an expression) second, ranking order (ascending, or descending). Here second argument is optional, and by default it is assigned as DESC (descending).
Rank Function will assign the same rank to identical value. For example, if we have 100, 200, 200, 400 then the function will return the ranks as 1, 2, 2, 4 in ascending order
TIP: Click on the arrow that we rounded to show or hide the existing function. You can search for the required function, and then check its syntax by selecting one.
Once you click on the OK button, tableau will assign the ranks for each record based on the Yearly Income values
Edit Tableau Rank
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 Compute using option as Table (down) by default.
Let us change the Compute using option from Table (down) to Specified Dimensions (Occupation, Last name, and First Name). Note, we are selecting the Occupation dimension for the Restarting every option. This will allow tableau to assign the ranking for each occupation. Or we can simply say, Rank yearly income partitioned by Occupation.
Now you can see the result. It is calculating the Rank for each Partition (here, it is Occupation)
RANK_DENSE
In this example, we are using the RANK_DENSE function. This function accepts two arguments first, aggregated measure (or an expression) second, ranking order (ascending, or descending). Here, second argument is optional, and by default it will assign DESC (descending).
Rank_dense Function will assign the same rank to identical value but it will not skip the next rank. For example, if we have 100, 200, 200, 400 then the function will return the ranks as 1, 2, 2, 3 in ascending order. In order to change the existing table calculation, please click on the down arrow beside the table calculation and select the Edit.. option as shown below.
Let us change the function name from RANK to RANK_DENSE or write down your custom function and click OK.
As you can see the result, It is calculating the Dense Rank for each Partition (here, it is Occupation)
RANK_MODIFIED
In this example we are using the RANK_MODIFIED function. This function accepts two arguments first, aggregated measure (or an expression) second, ranking order (ascending, or descending). Here, second argument is optional, and by default it will assign DESC (descending).
Rank_Modified Function will assign the same rank to identical value. The Highest value will be ranked as 1, and the next two identical values will be ranked as 3. For example, if we have 100, 200, 200, 400 then the function will return the ranks as 1, 3, 3, 4 in ascending order
Now you can see the result. It is calculating the Modified Rank for each Occupation
RANK_PERCENTILE
In this example we are using the RANK_PERCENTILE function. This function accepts two arguments one, aggregated measure (or an expression) second, ranking order (ascending, or descending). Here, second argument is optional, and by default it is assigning the DESC (descending).
Now you can see the result. It is calculating the Percentile Rank for each Occupation
RANK_UNIQUE
In this example we are using the RANK_MODIFIED function. This function accepts two arguments first, an expression second, ranking order (ascending, or descending). Here, second argument is optional, and by default it will assign DESC (descending).
Rank_UNIQUE Function will assign the unique rank to identical value. For example, if we have 100, 200, 200, 400 then the function will return the ranks as 1, 2, 3, 4 in ascending order
As you can see the result. It is assigning the Unique Rank for each items present in the Occupation
Thank You for Visiting Our Blog
Leave a Reply