This blog will show you how to rank your data using a table calculation in Tableau. For this example, I will be using Tableau Sample Superstore data. The aim is to rank our product sub-categories within each region, by sales.
Our final view should look like this. We have the Top 5 product sub-categories with the highest sales for each region.
Lets get building! We want to start with a blank sheet, onto this we drag sales into the columns, and then region, followed by sub-category, into the rows. Our view will look like this, don't worry if it is a little bit messy right now.
Next, we drag and drop sales into the rows shelf. We can do this by holding down 'ctrl' and dragging sales from columns (this will duplicate it), or by simply selecting it again from the full list of data fields.
Following this, we right click on sales to select discrete to clean up the view before we attempt the Table Calculation.
Now, it is time to calculate our rank. Right click on the (blue) sum of sales in the rows shelf and select 'add table calculation'.
This will open the following window.
Here we select our desired calculation, and the dimensions for grouping / calculating our rank.
We select 'Descending' because we want to rank highest sales as '1' followed by the second highest, and so on. In the specific dimensions field, we deselect the data field for which we want the ranking to restart; so in this case, we want to rank the sales of each sub-category, restarting for every region. Once we click out of the Table Calculation, we can see our view change and our rankings appear to be visible in the red box, although it is difficult to read due to the number of product sub-categories in the view and the lack of sorting. Lets tidy up this view.
First we need to drag our new sales table calculation (the blue pill in our rows shelf with the triangle indicating it is a table calculation) between region and sub-category. This will automatically sort our bars by rank.
However, this is still very crowded, so lets filter to only see the Top 5 sub-categories for each region. Holding down 'ctrl' again, select the blue sales table calculation in the rows shelf to duplicate it, and drag it into filters, from there you can select 1 to 5. This keeps only the top 5 sub-categories in our view.
Our view should look like this now, your last formatting options are to add region onto colour and add labels to your bars (if so desired).
With those final touches, you should get to the version shown at the start of this blog. Happy ranking!