Why the row-level/aggregate-level difference matters

Let's say you created a Discount % (row-level) calculation with the following code:

[Discount] / [Rent]

The code differs from the aggregate calculation you created previously, which had the following code:

SUM([Discount])/SUM([Rent]) 

Here is the dramatic difference in results:

Why is there such a difference in the results? It's a result of the way the calculations were performed.

Notice that Discount % (row-level) appears on the Measure Values shelf as a SUM. That's because the calculation is a row-level calculation, so it gets calculated row by row and then aggregated as a measure after all row-level values have been determined. The 60% value you see is actually a sum of percentages that were calculated in each record of underlying data.

In fact, the row-level calculation and then final aggregation is performed like this:

Contrast that with the way the aggregate-level calculation is performed. Notice that the aggregation that's listed on the active field on the Measure Values shelf in the view is AGG, and not SUM. This indicates that you have defined the aggregation in the calculation. Tableau is not aggregating the results further. Here is how the aggregate-level calculation is performed:

It is vital to understand the difference between row-level and aggregate-level calculations to ensure you are getting the results you expect and need. In general, use row-level calculations when you are certain that you will use either the value as a dimension or that an aggregation of the row-level values will make sense. Use aggregate calculations if aggregations must be performed prior to other operations.

One of the most common error messages that's encountered while writing Tableau calculations is Cannot mix aggregate and non-aggregate arguments with this function. When you encounter this message, check your code to make sure you are not improperly mixing row-level fields and calculations with aggregate fields and calculations. For example, you cannot have something like [Discount] / SUM([Rent]).

This mixture of a row-level value (Discount) and the aggregation (SUM of Rent) is invalid.

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

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