Rank is a very powerful concept in Tableau and one of the widely used Table Calculations. It can be used in many situations along with other Helper functions like Index(), First(), Last() and Size(). It can also be very effectively leveraged for Pagination.
When building visualizations, the focus would be to show the best performing metrics/values. As seen in some of the other blogs, we have used different methods to prepare the Top N and Bottom N values. In this blog, we will focus strictly on the Top Ranks across multiple Dimensions within the same visualization for the same measure. Though this can be easily achieved by placing multiple sheets on a Dashboard and providing a Drill down approach though action filters, it might not be an acceptable solution for all end users. This blog provides a step by step approach to solve this using Calculations and Table Calculations where all pieces are put together in a single visualization.
For this example, we will be using 2010 Population data for some of the cities across USA. We have taken 161 Cities, 25 States. For testing purposes, the data is split into 5 Regions: North, South, East, West and Central.
This blog will be approached more like a use case. The requirements are:
1) Show the Top Cities by Population and their Ranks. This is very straight forward.
2) Show the Top Cities by Population within each State with their Ranks. If the need was to show within a single state, it could have been easily achieved using a Context Filter. But we need multiple States to be displayed.
3) Show the Top States by Population within each Region with their Ranks. Slightly getting complicated here because there is a need for Hierarchy Ranking.
4) Show Top Regions by Population and their Ranks. This is the most complicated step as it requires double Hierarchy ranking or nested ranking within all the above-mentioned Dimensions.
5) Provide a single control for all three Ranks. This step is achieved by a Parameter which can select all the three Ranks
The requirements look a little complicated at first, but as we slowly go into each of the pieces, it becomes better.
Though this example works in most cases, we need to be a little careful with the calculation when there is a Many : Many relationship (duplicate matches).