Within Tableau, you are able to perform calculations on aggregated fields, but also with non-aggregated fields. It is important to understand the difference between these two. Since the answer you are trying to obtain might not be exactly what you are looking for. If you make the mistake of using the other method. In this blog, I will go through what I mean by aggregated and non-aggregated fields. After that, I will move onto explaining the difference between them with an example.
Aggregations in Tableau
While performing analytics, Tableau by default will automatically aggregate or disaggregate your fields based on how you position them in your view. We have all seen the “Sales” field change to SUM(Sales) when we put it on the row shelf for example. That is a type of aggregation. Aggregations allow summarisation of your data which increases the value of the information by preparing your data to be viewed in visualisations. Where Tableau excels in.
Measures and dimensions can both be aggregated, however it is most commonly measures that are aggregated. Measures are automatically aggregated when put it the view. To change the aggregation of a field, one of the ways is to right-click a pill on the view and change the aggregation from there. As default, the aggregation is to SUM. However, different type of aggregations for measures exist as follows:
Aggregations also exist for dimensions, however they are more limited while compared to measures:
For more information on aggregations, see the help documentation on Data Aggregation in Tableau.
Aggregations in Calculations
It is possible to change the aggregation of your fields from the data pane or from the shelves where your fields exist in. However, aggregations can also be configured as calculated fields as well. Using calculated fields allows the user to create their own data in a way that is not supplied by the data source itself. Therefore, it opens up the possibility to change the means of aggregation when combining fields.
For example lets say I have Profit and Sales in my data source and I want to create a profit to sales ratio. I will need to create a calculated field for this instance. However, it is very important to define what exactly I want to see from this calculation. And it depends on using aggregated or non-aggregated fields in my calculation.
In the profit to sales ratio example, the calculation can be written in two ways that will result in completely different results. The first one is with a SUM wrapped around each field as follows:
Or without the aggregation as follows:
Understanding the Difference
To understand the difference, it is important to understand how Tableau interprets these two calculations. In the first example, Tableau is summing all profit and sales values from each row, and dividing them together. This results in a pre-aggregated field. Therefore, when this newly created field is put on the view, the pill will have an AGG() wrapped around the field. Depending on your dimensions on the view, the calculation will change to only include those members from that dimension. In the below example this calculation is used against the sub-category field:
It can be observed that the numbers seem correct. They all stay in the ratio range. However if I use the same example with the calculation without aggregation, I get different results as follows:
The result is definitely not right. If you notice, instead of AGG, I get the default SUM aggregation to my non-aggregated calculation. That is because Tableau has automatically aggregated that field once put on the view. It is in Tableau’s nature, it wants to aggregate.
What the non-aggregated field does is that Tableau calculates the profit to sales ratio for each row and stores that value. And at the end in this case, it is summing up all the calculated values together. If we make this profit ratio field a dimension instead of a measure, it makes more sense how it is done:
Each product in the subcategories have their profit ratio calculated separately. What Tableau Was doing was summing up all these individually calculated values at the end. Therefore for the example of calculating the profit ratio, the calculation should be made with aggregated fields.
Visit our site The Information Lab NL to see more blog posts, training and consultancy services regarding Tableau, Alteryx and Snowflake.