KNOWLEDGE BASE

Moving Average Returns Incorrect Values


Published: 18 Sep 2015
Last Modified Date: 21 Nov 2017

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.
Did this article resolve the issue?