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:
- What percentage of sales is contributed by each product in a particular zone?
- What percentage of sales is contributed by each zone for a particular product?
- What is the percentage of total sales for each product in each zone?
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.
- We will begin by creating a new sheet and renaming it Table Calculations-percent of total.
- Let us then drag the Category field from the Dimensions pane and drop it into the Rows shelf. Followed by dragging the Region field and dropping it into the Columns shelf.
- Next, let us drag and drop Sales into the Text shelf.
- Once we do that, let us then make sure to enable both the Row Totals as well as Column Totals from the Analysis tab in the toolbar or the Analytics pane. Refer to the following screenshot:
- After enabling the totals, our view will be updated, as shown in the following screenshot:
- Let us now click on the dropdown of the green pill in the Text shelf, which is basically our SUM(Sales). Let us select the option that says Quick Table Calculation. Refer to the following screenshot:
- We will then select the Percent of Total option and once we do that, we will get a view as shown in the following screenshot:
- Now, the view does show some percentages, and if we notice carefully we are getting 100 percent for each row. That means that the percentages are computed using the Row Total and it answers the question of What percentage of sales is contributed by each Region for each Product Category?
- Also, notice the SUM(Sales) green pill in the Text shelf. We will see a small triangle or delta symbol. This is basically an indicator that a Table Calculation is being used. Let us again click on the SUM(Sales) in the Text shelf and this time select the Compute using option. Refer to the following screenshot:
- We will see that by default, the Table(Across) option is selected and hence we are getting the Row Total. If we select the Table(Down) option, we will get the Column Total. Refer to the following screenshot:
- Also, if we select the Table option under Compute using, it will give percentages out of the Grand Total. Refer to the following screenshot:
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.