INDEX vs RANK

Index and Rank are part of Tableau table calculation. The main difference between the two is that Index computes by looking at the physical position of the record without any regards to the value whereas Rank computes the data row based on the predetermined measure in the calculation.

In the example shown below, we are looking at the number of products a manufacturer provides. Our view is currently set to show the top 30 manufacturers by the number of products they supply in descending order.

Index, can be called upon by simply typing INDEX() in a calculated field like shown below.

The configuration for the rank function is the following. RANK(AGG([Measure]), ‘asc’/’desc’). Below we see both Index and Rank in the view. We can see both Index and Rank show the same results until we reach DAX and Tenex which both provide 16 products, Index continued down the list in increments of 1 whereas Rank shows 19 for both. Rank goes on to skip the number 20 and moves straight to 21.This is because DAX and Tenex take up both position 19 and 20 but with rank is shown the first occurrence of that duplicated value.

RANK vs RANK_MODIFIED

Unlike Rank which shows the position of the first time a value is seen, RANK_MODIFIED shows the last occurrence. Take the manufacturer name Advantus, Bush, Memorex and O’Sullivan we see that they provide the same number of products 13.0. The Index value ranges from 22-25 for these manufacturers, Rank is showing the value 22 (the first position seen) and Rank_modified is showing 25 the last position seen for the value 13.

As mentioned before, index is sensitive to the order of the rows whereas as rank responded to the values in the row. We can see from the example below that index is numbering the Sub-Category by the position it appears on the rows whereas as rank is ordered by the sum of sales in descending ordered with the highest showing position one.

The naming of Rank is slightly different if you are using it as a table calc or calculated Field. The Rank Unique produces the same output as index.