To aggregate or not to aggregate fields?

The main rule with Calculated Field is to think about aggregation when you create a new Measure. As you know, a Measure is, by default, aggregated and so are Calculated Measures. You can specify the aggregation inside the formula or not. If you aggregate the data inside the formula, the pill of the Calculated Measure will start with AGG. If you don't specify the aggregation in the formula, a default one is added when you use the Calculated Measure, as for any normal Measure.

For example, the Calculated Measure Profit Ratio, in the Sample - Superstore Data Source, contains the aggregations inside the formula, so its pill starts with AGG displayed as follows:

A Calculated Field cannot contain aggregated and non-aggregated fields in the same formula. Remember that you can aggregate a Dimension with the ATTR() function.

Often, there is a significant difference between adding the aggregation inside the formula and not. For the calculation of Profit Ratio, Tableau first aggregates the Profit and the Sales separately, then divides the two results.

An incorrect way of calculating the Profit Ratio would be [Profit]/[Sales]. With this calculation, Tableau divided the value of the Profit by the Sales at each line of the Data Source, then aggregates the result of all the divisions. To illustrate, I created a Calculated Field, Wrong Profit Ratio, with the incorrect formula. Here's the difference between the two calculations:

As you can see, if Tableau sums the result of all the divisions rather than dividing two aggregated values, the result is incorrect. Always keep this principle in mind. 

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.224.59.192