Table calculations are one of the advanced levels of calculations available in Tableau. They are also one of the most powerful features of Tableau. The reason for calling them Table Calculations is because we can define the scope and direction of the calculation based on the table/view.
To elaborate this point further, let's take a look at an example. See the following screenshot:
The preceding screenshot shows the value of three products sold across three zones. The information is arranged in a crosstab manner where the zones are placed in the columns and products are placed in the rows. Also shown are the Row Totals, that is, totals for each product across all the zones and Column Totals, that is, totals for each zone across all the products and finally we also have the Grand Total, which is the total for the entire view, that is, across all products and across all the zones.
Now, if we want to show percentages instead of just looking at the absolute numbers, we would have to do a simple calculation, where we would divide each value with the total value. However, the dilemma is that there are three different total values and each division will give us separate results, so which total value do we use?
Well, the answer depends completely on the business question. So, say the questions were as follows:
For the preceding set of questions, and the way the table is currently structured, that is, zones in columns and products in rows, we would use the Column Total, Row Total, and Grand Total, respectively.
Now, imagine swapping the Rows and Columns. Refer to the following screenshot:
The screenshot shows products as columns and zones as rows. In this case, our selections of total would change. We will use Row Total for the first question, Column Total for the second, and finally the Grand Total for the third question.
Thus, we have seen how certain calculations will have to be modified depending on the way the table or the view is structured.
Now that we have understood this example, let us recreate a similar view in Tableau and understand Table calculations in a bit more detail.
We will use the Sales, Region, and Category field from the Orders sheet of Sample - Superstore.xlsx
data for the following recipe. Let us get started.
In the preceding recipe, we saw how to create and use a Quick Table Calculation. The reason why they are called Quick is because they are readily available and the reason why they are called Table Calculations is because we can change the scope and direction of the calculation.
Also, if we double click on the Green pill in the Text shelf, which is called SUM(Sales), then we can actually see the formula that is being used by Tableau in order to execute the output. Refer to the following screenshot:
The Quick Table Calculation option does computations on the fly; however, if we want to save this calculation for later use, then we can do so by pressing Ctrl on our keyboards and dragging and dropping the Green pill from the Text shelf into the Measures pane. Refer to the following screenshot:
We can then name this calculation as Percent of Total Sales. Once we do that, we will see that the Green pill in the Text shelf, which was earlier called SUM(Sales), will now be called Percent of Total Sales.
What we saw in the preceding recipe was a quick and easy way to get a Table Calculation. There are certain calculations that are required quite often in business and these are made readily available to us in the form of Quick Table Calculations in Tableau. We can either make use of these pre-defined and pre-computed calculations by clicking on the field and selecting the Quick Table Calculations option, or we can even create them from scratch by creating a calculated field and then selecting the Table Calculation option from the dropdown. This will populate the list of all the Table Calculation functions available in Tableau. Refer to the following screenshot:
Refer to https://onlinehelp.tableau.com/current/pro/desktop/en-us/functions_functions_tablecalculation.html link to know more about the Table Calculations in Tableau:
There is also an interesting article that gives a couple of examples of Table Calculations. The article even has the options to download the packaged workbooks for one's perusal. The link to the article is http://www.tableau.com/table-calculations.
Another important link to refer to is http://onlinehelp.tableau.com/current/pro/desktop/en-us/calculations_tablecalculations_understanding_addressing.html. This gives a detailed explanation on the direction and scope or as we call it in Tableau, the Addressing and Partitioning of the Table Calculation.
The preceding links will help us understand more about Table calculations. However, to know more about all the various functions that are available with Tableau, please refer to http://onlinehelp.tableau.com/current/pro/desktop/en-us/functions.html.