Understanding Table Calculations, Pt1
Tableau Table Calculations are powerful ways of performing calculations on different measures over the length of the table, such as 'percent of total' and 'running sum', but there are of course many other more exotic use cases. Table calculations are performed upon a specific region of a table.
All tableau views can be considered at their most fundamental level as a table, there are nearly always elements in place on row and column shelves (though having elements in place on these particular shelves is not always a requirement for the table calculation to function)
Working with table calculations requires an understanding of addressing and partitioning, or how the calculation is applied. The addressing fields are those fields that define how you are computing through the table, and partitioning fields are how you are dividing the table up. An example is always worthwhile about now.
To begin with we’re going to look at the most common way of performing a table calculation Table(Across)
Here we have a table, in the columns we have Category followed by region, and in the rows we have Year and Quarter. The Fields running across the table are the addressing fields (in orange) and the fields in blue are the partitions(here the partitioning will be performed at the lowest level of detail that of Year/Quarter.
so the calculation will be performed within the orange boxes below.
If this was a % of total the total would be calculated for each Year/Quarter and the % would relate only to that Year/Quarter
Lets contrast this with Table(down), here the addressing is now the Year/Quarter and the partitioning is Category/Region, this will result in partitions that look like this:
Here we can now see that the % of total will be computed with the total being the sum of all the values in each category/region, and the percentages being the percentage of that total.
Addressing and partitioning can be a very powerful tool to manipulate your views and data but care should be taken always that you are arranging the addressing and partitioning fields in the correct way. Too begin with it is worth sticking to the addressing and partitioning schemes built into tableau, and trying out the different schemes to see how they work.
Now that we've looked at table calculations for text tables lets start looking at them in terms of charts. Going back to the beginning tableau considers charts as tables that to then renders as a graphical view using a series of rules.
Applying table calculations to charts is in some ways easier especially when dealing with the more common table calculations, the running sum and the percent of total, as the shapes and colours will be easily recognisable.
A percent of total bar chart is one of the chart types that I have encountered most often. Here we have a bar chart showing the sales each month broken down with category on the colour, but what i’m interested in is what proportion of sales each month were of a certain category and how do those proportions change month by month.
When I add the percent of total to the sales measure I get a chart that looks like this:
Chart with % of Total Quick Table Calculation
Now I can tell straight away that this is not quite right it looks basically the same as the base chart, looking further into this I find that its using the default calculation of table across so each segment of each column is the % of the sum of sales over the entire range of months.
So changing this to pane across might give us the correct graph as it is now including the date in the partitioning so we wont be using the whole table when computing the total to find the percentage of.
% of Total Pane (Across)
This is better, but still not right, what we actually need is that this is partitioned at a lower level of detail, and in this case that would be by cell. In this view a cell is every unique combination of year and month.
% of Total Cell
This is more like it, now we can clearly see for each month the proportion of sales of each category.
But lets change this around again lets place category on the columns shelf.
Now we can see that computing by cell in this case is not going to cut it, we still want to compute the % of total using order date as the partition.
So we use compute by pane across again and voila, we have the same view as the proper % of total we had before but instead of a stacked bar we have a separated bar chart.
Hopefully this has been a good quick introduction to table calculations, as you might now be realising they are an incredibly powerful tool for creating views within tableau.