Rank function is used to give positions (rank) to any measure in the data set. Tableau can rank measure in the following ways:
- Rank: The rank function in Tableau accepts two arguments: aggregated measure and ranking order (optional) with a default value of desc.
- Rank_dense: The rank_dense also accepts the two arguments: aggregated measure and ranking order. This assigns the same rank to the same values but doesn’t stop there and keeps incrementing with the other values. For instance, if you have values 10, 20, 20, 30, then ranks will be 1, 2, 2, 3.
- Rank_modified: The rank_modified assigns the same rank to similar values.
- Rank_unique: The rank_unique assigns a unique rank to each and every value. For example, If the values are 10, 20, 20, 30 then the assigned ranks will be 1,2,3,4 respectively.
Now, let’s go step by step and see how to use rank function in tableau.
- Open Tableau and we will be able to see the below screen. Connect to a file or server (based on where you input data is present) using the connect option present on the left side of the Tableau landing page.
- For this article, we are going to use the “Sample- Superstore” data store present and the below screenshot will tell us how the data looks like.
- Click on the option “Sheet 1” present on the bottom left corner and we will be able to see a window like shown below and this is where we prepare our dashboards.
- Choose the measure on which we want to apply rank function, here I am going to apply a rank function on Sales measure by taking Region present in dimension as rows. Drag Region dimension to row and drag Sales measure into the marks card and the window will look like the way shown below.
- To give rank to the sales measure, we have to create a new calculated field first and we do it by giving a right-click on the sales in measures pane. And we can see a new menu appeared like the screenshot here. Now we have to go to create and then click on the calculated field option present here.
- After clicking on the calculated field, one new window will pop up and in there we can see that “All” is selected in the drop-down present to the right.
- As mentioned earlier, rank functions will be in table calculations so we have to open the drop-down and select “Table Calculations” and we will be able to see all the rank functions and little information about the functions as shown below.
- As we want the rank function to be applied on the aggregate of sales measure, the calculation will be “Rank(Sum([Sales]))” and we can give the name to this calculation. Here in this example, I am going to name the calculation as Rank.
Popular Course in this category
Tableau Training (4 Courses, 6+ Projects)4 Online Courses | 6 Hands-on Projects | 39+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (6,833 ratings)
- After giving the calculation, we will go ahead and click on Apply and Ok for this calculation to reflect in the Measures section. After we see Rank in the Measures pane we should drag it onto the aggregate of sales present in the dashboard area, and the dashboard will look like the way shown below
- Here, in the marks pane both aggregate of sales is set to “details”, to have a better understanding let us change it to “Text” to do this we just have to click on the symbol on the right of measure in marks card and it will open a window with “Text” as one of the options, select it and then the dashboard will look like the way shown below.
So, after this is done from the values provided we can see that the highest rank (1) is given to the region with the highest value of sales ($725,458). If we want to rank to be shown in a separate column, then change the order of steps by doing Step-10 first and then move on to Step-9.
If we want to do any changes in the formatting, we have to click on the drop-down present to the left on the Rank section in Measure values pane and here we can do all the changes in format (Axis and Pane formats) and the window will look similar to the one shown below.
Here we are having all different values and If there is the same value who will be given the better rank? Different users have different requirements to solve this tableau provides us with a few more additional features Rank Dense, Rank Unique and Rank Modified. And also tableau by default picks some properties and one of them is sorting in Descending order.
So, let us see how these things help us by meeting our requirements.