Table calculations allow you to perform calculations for items within the current view, whether it be across a table, cell or pane (with the added bonus of having further customisations available to you, should you know how to use them). As the adage goes, with great power, comes great responsibility.
So, taking a step back from the intricacies of working out which dimensions to compute by, lets instead work on forming a more meaningful visualisation than what is pictured below, which of course incorporates table calcs.
This is achieved by RANKING table calculations, which is not a native function in Tableau.
The view pictured above shows sum([Sales]) as well as % of Total Profit per Sub-Category. This is using the Sample – Superstore data. At the moment, we can sort on Fields such as Sales but not on % of Total Profit as it is a table calculation.
These are the steps to follow in order to rank dimensions over a table calculation.
Start by double clicking on the Sub-Category pill from the data pane.
Click and drag both the Sales and Profit pill into the column shelf.
Right click on the Profit pill and add a quick table calculation (in this case % of total profit).
Right click the axis for % of total profit and edit the axis so that it is fixed with the fixed end being 1.
Next, hold down shift while left-clicking on the profit pill in the column shelf and drag it into the data pane. This essentially copies the calculations performed and saves it on the data pane. Name it something meaningful like ‘% Total Profit’.
Next, create a calculated field which ranks the % Total Profit (RANK([% Total Profit], ‘desc’)) and name it ‘Rank % Total’.
Right click on the calculated field on the data pane and convert it to discrete.
Click and drag that pill onto the rows shelf, to the left of Sub-Category, and it should automatically sort Sub-Category on % Total Profit.
The finished viz should look like below!
