I have created a parameter (Remove Outlier) to remove data where Daily Average Duration is greater than the selected number.
Then I would like the 'Running Avg' worksheet to show me the latest result (running average as at the last day – I have done this by using lookup function (last) by each service type.
But, the result does not change with the parameter selection and even without the parameter selection, the result seems to only work for service type F (refer to more comments below).
Here is my formula:
Running Average (Test) =
IF [Daily Average Duration] <= [Remove Outlier] then
RUNNING_SUM(SUM([Duration Weekday]))/RUNNING_SUM(COUNT([Transaction Number]))
Note that I don’t use the moving average formula, because it averages the daily average duration. Whereas I want it to average the running sum.
Then I used the lookup formula to get the latest number.
Max Running Average (Test)
LOOKUP(([Running Average (Test)]),LAST())
It also seems like it is working for Service Type F (6.10) but not for the other service type (for example A shows 9.92 in the Running Avg sheet, but 9.64 in Moving Avg Parameter sheet.