Last week, we discussed in the class of how to simplify complex visualizations in Tableau by creating new data from existing data through calculated fields.
Though it is best to prepare our data as much as possible before it gets to Tableau, there are many reasons to leverage the calculated fields functionality in Tableau. Few of them are:
- To segment your data in new ways on the fly
- To prove a concept such as a new dimension or measure before making it a permanent field in the underlying data
- To filter out unwanted results for better analyses
- To take advantage of the power of parameters, putting choice in the hands of your end users
- To calculate ratios across many different variables in Tableau, saving valuable database processing and storage resources
As we know it is important to understand the data before making any visualizations. Understanding the data also includes knowing the nature of the data based on which we can decide in which family of calculation our data belong. There are three major families of calculated fields in Tableau:
Non-aggregate calculations
These are the simplest type of calculation. Non-aggregate calculations are performed for each row in the underlying data, rather than being performed on aggregated data (such as you would find in a pivot table or Tableau view).
It is a calculated field which does not use any functions from the ‘Aggregation’ function group. For example: [Sales] – [Cost] would be a non-aggregated calculation.
Aggregate calculations
Aggregate calculations are those that use aggregate functions. Examples of aggregate functions are SUM, AVG, MAX & MIN (there are a few others). Therefore an example of an aggregate calculation would be: Profit Ratio = SUM(Profit) / SUM(Sales).
When we drag and drop a measure in Tableau, it is automatically aggregated. The default is sum. The primary difference between aggregate and non-aggregate calculations is that aggregate calculations often can’t be sensibly calculated for each row in the underlying data set – it normally only makes sense to calculate them when the data is aggregated.
Table calculations:
Table calculations allows us to compare two or more separate measures in our data set, it allows us to compare a singular measure to itself (the only way to compare a measure against itself). These are the calculations which are applied to the values in the entire table. For example, for calculating a running total or running average we need to apply a single method of calculation to an entire column. Such calculations cannot be performed on some selected rows.
When writing any calculation, make sure to know exactly what you want to do. There are many functions and table calculations within the powerhouse of Tableau which can be utilized to create the calculated fields for presentation of data in a pictorial or graphical format. Keep exploring. Keep learning.
Sources:
http://www.clearlyandsimply.com/clearly_and_simply/2010/10/calculated-fields-in-tableau.html
https://www.tableau.com/about/blog/2017/2/top-10-tableau-table-calculations-65417