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:

[2013 Sales]

IF YEAR([Order Date]) = 2013 THEN [Sales] END

[2014 Sales]

IF YEAR([Order Date]) = 2014 THEN [Sales] END

[Difference]

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.

## Commentaires

Very Helpful.

Thank you.

Thanks for posting this. I love stuff like this because it shows how much I really still have to learn about using Tableau.

I might be missing something, but for exercise 4 in the deck, the "Start 4" workbook uses a different data set from the "Solution 4" workbook. Both only have rows from 2014 & 2015, but the solution data set goes from Jan 1, 14 through Sep 25, 15 and the Start 4 data goes from Sep 8, 14 through Sep 8, 15.

Am I missing something in how I'm supposed to match the solution?

Excellent article! I might have found a typo, where you used INCLUDE and probably meant FIXED. See the calculation in the second paragraph from the bottom of #2.

## Ajouter un nouveau commentaire