KNOWLEDGE BASE

Unexpected Grand Totals for a Calculated Field Based on a Field Set to Total using SUM


Published: 13 Jan 2017
Last Modified Date: 26 Jan 2017

Issue

The grand total for a field that references another field, which is set to total using SUM, may be incorrect.

For example, the view shows average sales for each region with a grand total that is the sum of all region averages. Another field divides AVG(Sales) by SUM(Quantity), however the grand total for this field is not the same as dividing the grand total of AVG(Sales) by the grand total of SUM(Quantity).

Environment

Tableau Desktop

Resolution

Modify the calculation which is returning incorrect grand totals to use a formula that customizes the grand totals. In the customized grand total formula, the field that is set to total using SUM should be rewritten as a nested FIXED expression. For example, if AVG( [Sales] ) was set to total using SUM in the view, then the following calculation:

AVG( [Sales] )
/
SUM( [Quantity] )


Would become:

IF MIN( [Regions] ) = MAX( [Regions] )
THEN AVG( [Sales ] ) / SUM( [Quantity] )
ELSE
SUM(
{ FIXED : SUM( { FIXED [Regions] : AVG( [Sales] ) } ) }
)
/
SUM( [Quantity] )

END
Did this article resolve the issue?