Moving Average Returns Incorrect Values

Published: 18 Sep 2015
Last Modified Date: 29 Nov 2019

Question

When using the Moving Average quick table calculation or the WINDOW_AVG function the results may differ from the aggregated value over the same period.

Environment

Tableau Desktop

Answer

Table Calculations always calculate based on the values of the given measure in the view. For averages and aggregated ratios, this means that the quick calculation produces a value based on averaging those averages or ratios. This is not equivalent to calculating the average or ratio over the moving period.

(x + y) / z + (a +b) / c ≠ (x + y + a + b) / (z + c)

In order to create the correct calculation, use WINDOW_SUM to calculate numerator and denominator separately. For averages the denominator is often [Number of Records].

Please see the attached workbook for a demonstration of an Average and a Ratio.