When trying to choose between basic calculations and table calculations, the important question is: Do I already have all the data values I need on the visualisation?
If the answer is yes, then you can calculate the answer without further interaction with the data source. This will often be faster as there is less data that needs to be processed (i.e. we are just computing using the aggregated values from the result set).
If you do not, then you have no choice but to go to the underlying data source to calculate the answer.
Consider the following example in which we ask: What is the 90th percentile of our order details, shown by country?
Both sides of this dashboard answer the question. If you were just interested in the 90th percentile value and didn’t need to determine further insights, then the chart on the left would be optimal. It provides a minimal result set (one number per country) via a basic aggregation PCT90([Sales]) which is calculated in the underlying data source.
However, if you wanted to gain further insights (e.g. understand the greater distribution and identify outliers) or add other aggregations (e.g. you also wanted the median values) then the chart on the right allows you to do that without further queries. The initial query to return all the order detail records (the green dots) provides all the data necessary to locally compute the 90th percentile as well as explore other insights.
One of the key takeaways from this post is that the layout of the viz matters. As we’ve already discussed above, the viz design will impact how much data you initially return from the data source. This is an important factor in determining your approach.
However, there are situations where, although you have all the data you need in your result set, it is not possible to achieve the required layout using a table calculation. So you also need to ask: Does the layout of the viz permit me to use a table calc?
Consider the following example in which we ask for the year-over-year difference in sales in two formats, one as a chart and the other as a table:
The top half of this dashboard is easily achieved using a table calculation. Simply duplicate the Sales field and apply a difference quick-table calculation run across the Order Date dimension. However, if you try to then convert that computation structure into a table, you end up with the following:
You will realise that it’s not possible to achieve the specified layout with a table calculation as you need the Year dimension with the Measure Names dimension nested inside. Tableau cannot suppress the “Difference in Sales” row for 2013. So in this example, your only option is to use basic calculations:
IF YEAR([Order Date]) = 2013 THEN [Sales] END
IF YEAR([Order Date]) = 2014 THEN [Sales] END
SUM([2014 Sales]) – SUM([2013 Sales])
This approach allows you to just have the Measure Names dimension which you can sort to meet the layout requirements.