# Stuck creating moving average of calculated percentage

**Nik Sargent**Oct 19, 2016 6:59 AM

This problem is currently driving me crackers in Tableau Desktop v9.2.5. I've googled to try and find a solution, but haven't (or haven't been able to recognise it). I'm a newbie to tableau of about a month.

It's the problem of creating a moving average of a percentage.

My raw data is just counts of events (think "error codes" or "outcomes") by day , so I am interested in the % split of "outcomes" on each day, to build a trend line.

I can use the built in table calculation functionality of tableau to calculate a percentage for each Outcome calculated by Table-down.. That works beautifully to tell me the split of outcomes.

Then I need a moving average. So, initially I used the built in table calculation dialogue to create that. It requires me to specify the calculation type as moving->average, but this (of course) is based on the outcomes (i.e. the raw data) - i.e. absolute numbers, not the % proportion, which was calculated.

So, I set a secondary calculation to be performed on % of total, and eventually managed to get the partitioning set up so that it all looked pretty good.

Except it isn't and it's taken me a few days to work out why. Really this calculation needs reversing, in my use case.

What tableau is doing is, for each outcome (i.e. row) is taking an average for row outcome over the moving time window (say 3 days). So far so good.

It is ALSO doing that for the total (i.e. notionally the column total). So the first observation is - because I have gaps in my data - this leads to the sum of all averages (i.e. the 3 day average in each row) not adding up to the total (i.e. the 3 day average of each day's sum). (I have also modelled all this in excel to double check & reverse engineer, and get the same answer)

If you then add the secondary calculation for percentage, Tableau calculates down the column, and since the column doesn't total properly, the percentages don't (necessarily) add up to 100%.

I have gaps in the table where on certain days certain outcomes did not occur, so there is no record, and (like Excel) the tableau average function is only working the number of values that exist, not the number of days in the moving window.

There are two solutions I am looking for and either would probably do, even though they give different answers. The first is to somehow account for the missing data in the averaging of the moving window. Toggling the "Null if not enough values" option doesn't do this.

The second would be to essentially reverse the way the calculation is made and generate the percentages on the outcomes for each day first, and then have the moving average calculated just against those percentages... This would work in my use case, because those percentages are essentially just KPI scores, and they may as well just be a number from 1 - 10. So even though they are a percentage, they essentially are just an index number. So averaging them is meaningful in this use case..

I could make a case for either solution but I am struggling to create them...

my numbers look like this (reproduced in excel):

Date | 15-Aug-16 | 16-Aug-16 | 17-Aug-16 | Average |

Total -> | 11 | 23 | 18 | 17.33333333 |

P | 4 | 4 | 1 | 3 |

V | 7 | 16 | 16 | 13 |

A | 2 | 1 | 1.5 | |

T | 1 | 1 | ||

Y | 0 | 0 |

and the various methods for calculating an average % over those three days (based on the above data) yield this:

total | 11 | 23 | 18 | left (average of % across) | table-down sum (tableau) | proportion of self |

P | 36% | 17% | 6% | 19.8% | 17.3% | 16.2% |

V | 64% | 70% | 89% | 74.0% | 75.0% | 70.3% |

A | 0% | 9% | 6% | 4.8% | 8.7% | 8.1% |

T | 0% | 4% | 0% | 1.4% | 5.8% | 5.4% |

Y | 0% | 0% | 0% | 0.0% | 0.0% | 0.0% |

. | . | . | total | 100% | 107% | 100% |

It should be noted that if you complete the blank cells with zeros, then the tableau result becomes the same as the "proportion of self" result and totals 100% - ie the rightmost two results become the same. To be fair, I would probably prefer the "left (average of % across)" result, as it is simply the mean of the daily percentages, and for this use case, it is probably a better measure.

Any help would be appreciated

total | 11 | 23 | 18 | left (average of %) | table-down sum | proportion of self |

PNN | 36% | 17% | 6% | 19.8% | 17.3% | 16.2% |

VNN | 64% | 70% | 89% | 74.0% | 75.0% | 70.3% |

ANN | 0% | 9% | 6% | 4.8% | 8.7% | 8.1% |

TNN | 0% | 4% | 0% | 1.4% | 5.8% | 5.4% |

YNN | 0% | 0% | 0% | 0.0% | 0.0% | 0.0% |

total | 100% | 107% | 100% |