In the previous recipe, we looked at String manipulations. In this section, let us focus on manipulations on numbers. However, before we move to arithmetic calculations, we must first understand the difference between aggregated and disaggregated measures. To elaborate on this, let us see an example. The two formulae are SUM(Profit) / SUM(Sales) and SUM(Profit / Sales).
The question is, whether these two formulae will give the same result or will they give different results?
From what we have learnt in school, we know that the Bracket operations or Parentheses will be computed first. We know this methodology as PEMDAS (Parentheses, Exponents, Multiply, Divide, Add, Subtract). Since many people tend to say brackets instead of parentheses and orders instead of exponents, it is also referred to as BODMAS (Brackets, Orders, Divide, Multiply, Add, Subtract) or BEDMAS (Brackets, Exponents, Divide, Multiply, Add, Subtract).
Based on this knowledge, we can clearly say that the output of the previously listed formulae will be different.
In the first formula, which is SUM(Profit) / SUM(Sales), the way the formula will be executed is as follows:
However, in the second formula, which is SUM(Profit / Sales), the execution will be as follows:
To put this in a more simplistic way, in the first calculation, the individual measures are first aggregated by doing a summation of all the rows of Profit and all the rows for Sales and the aggregated Profit is then divided by the aggregated Sales. However, in the second calculation, each and every single transactional Profit value is first divided by the respective transactional Sales and then added up.
Let us actually try this example out in Tableau to gain more perspective.
As usual, let us continue with our existing workbook by creating a new worksheet. We will use the Profit field and Sales field from the Orders sheet from Sample - Superstore.xlsx
data. As stated in the preceding example, we will divide Profit by Sales in two ways using the formulae listed previously. Let us get started.
In the Profit Margin-aggregated calculation, we are asking Tableau to first compute a SUM([Profit]) for each Sub-Category and compute SUM([Sales]) for the same Sub-Category and then divide the two, whereas, as explained earlier in the Profit Margin-disaggregated calculation, we are dividing transactional Profit by transactional Sales for every record that is contributing to a particular Sub-Category. These transactional values are then added up at the Sub-Category level.
Also, notice both the calculations in the Measure Values shelf. We will see AGG as a prefix and it stands for Aggregated. Since we are using SUM in both the calculations, it is Aggregated. By default, all the fields will be aggregated in Tableau.
The point of this whole exercise was to clarify the difference between a field, say Profit, and an aggregated field, say SUM(Profit), and how this can make or break our calculations. We need to remember that when we say just Profit, we are referring to the transactional Profit and when we say SUM(Profit), we are referring to the Aggregated Profit.
In case we wish to turn off the default aggregation properties of Tableau for a particular sheet, then we can do so by unchecking the Aggregate Measures option from Analysis in the toolbar. For more information on aggregating and disaggregating data.