Getting Among Top 10% Tableau Viz Experts for Analytics
This article is about a particular topic that is usually not given much attention by beginners in the BI/Analytics field but can prove to be one of the most important functionalities to get a good level of insights from your data.
Sometimes, to get better business insights out of the data, one may think of going deeper and calculating the Moving averages, Percent Difference across variables, Running Total, Weighted Average, Percent of Total Sales, Transforming Values to Rankings and more.
All these can be achieved easily using Table Calculations in Tableau.
What are Table Calculations?
Table calculations are a special type of calculated field that apply transformations (i.e. calculations) on values within a visualization. These calculations are computed over local data (post-filtered data) within Tableau. Some important points here are:
- Table Calculations are done only on dimensions within the view. As long as we keep adding or removing new dimensions and changing the layout of Dimensions, Table calculations will change accordingly.
- In the above definition, Local data mean calculations occur after all filters are applied. And as we keep adding or removing filters, the calculations will change accordingly on the view.
This means that how you build a visualization, which dimensions are added, the layout of all dimensions used (i.e what is used in Rows/Columns and their order) and what data is filtered out all play a critically important role in making table calculations work as intended.
Tableau has provided easy solutions like Quick Table Calculations.
Here, you can find some pre-defined calculations that you don’t have to compose. They are already made available to the users, and quite helpful specially if one is just starting out with these kind of calculations.
Tableau takes the best guess on how you want calculations like running sum or percent of the total to work on your visualization. They are created by right-clicking on a Measure > ‘Quick Table Calculation’:
These are only some of the most frequently used workflows with data, where a user can directly select and use these calculations on the required dimensions.
As visible above, table calculations in Tableau’s interface are denoted by a triangle within the measure’s pill.
Also, one may witness above that the Grand Totals are computed across the table (i.e Row-wise). This is the default option that Tableau provides whenever we use Table Calculations.
However, this can be switched to other options as well based on the user requirements and how they want the Table Calculations to be computed on the local data.
This can be done by right-clicking on a Measure > ‘Compute Using’ and selecting the desired option:
Here, the Grand Totals have been computed down the table (i.e Column-wise).
Similarly, Tableau gives us other options as well to suit our requirements like Computing across an entire table or different panes, etc.
I won’t go into much detail regarding this as Tableau's official documentation has a detailed description of each of the Compute options along with insightful visualizations. Here is the direct link.
It’s advisable to go through this before moving forward as it’ll give a good clarification on the concept of Compute using different options.
ADDRESSING and PARTITIONING
When you add a table calculation, you must use all dimensions in the level of detail either for partitioning (scoping) or for addressing (direction).
The key to understanding table calculations is to know how these fields work.
- Partitioning fields define the scope: They break up the view into multiple partitions or sub-views. The table calculation is performed separately within each partition.
- Addressing fields define the direction: They define the “direction” that the calculation moves (for example, in calculating a running sum, or computing the difference between values).
Partitioning fields break the view up into multiple sub-views (or sub-tables), and then the table calculation is applied to the marks within each such partition. The direction in which the calculation moves (for example, in calculating a running sum, or computing the difference between values) is determined by the addressing fields.
The Dimensions that are used for Partitioning or Addressing can be seen when you open the Measure > ‘Edit Table Calculation’.
Within ‘Specific Dimensions’, each dimension in your view has to fall into one of two categories — partitioning and addressing.
Tableau uses a list of checkboxes to visually represent partitioning (unchecked) or addressing (checked).
When you add a table calculation using the Compute Using options, Tableau identifies some dimensions as addressing and others as partitioning automatically, as a result of your selections.
But when you use ‘Specific Dimensions’, then it’s up to you to determine which dimensions are for addressing and which are for partitioning.
For instance, we can see in the above example that the Dimensions — Category and Sub-Category are used for Addressing as the Table Calculation ‘Percent of Total’ is calculated on these fields, while
Year of Order Date is used for Partitioning as the Table Calculation is performed within each partition (i.e Year).
Similarly, we can use different Table Calculations in the same table like below:
Here, we have calculated the Percent of Total Sales, first using Table Down and then using Pane Down.
Notice the values for both are different, because they are answering different questions.
The first one calculates the proportion of Sales across Category and Region for that Year, hence Category and Region are used for Addressing while Year for Partitioning.
And the second one calculates the proportion of Sales across Region for that particular Category and particular Year, hence Region is used for Addressing while Category and Year for Partitioning.
This was a brief description of a much broader and Advanced topic.
I am going to come up with Part II of this article, focusing exclusively on Advanced Table Calculation and certain Use Cases to demonstrate how this can be used on Real Data to derive valuable insights.
Do remember to give a Clap if you liked this article, comment to suggest improvements, and Follow for more such topics related to Analytics.
