First of all, when using table calculations I recommend using fixed addressing (a set of one or more dimensions in the view) instead of relative (Table/Pane Across/Down). Just about any result you can get with relative addressing, you can get with fixed addressing. In this case, in your initial view addressing on Date_day gives you the same results as Pane (Down).
It seems like what you want is for the Load_Time_MA to be the moving average, but I'm not clear on what you want for that in the Grand Total row, what sort of aggregation do you want there? Is it the average of the average results for each hour? In that case, you're going to need to do an aggregation of the Load_Time_MA to return the correct results, because you're needing one aggregation in the detail rows and a separate aggregation in the grand total row. It might be possible to use the current technique with duplicated dimensions and a modified Compute Using for the aggregated calc, otherwise you'd need to do one of the following:
- set up a test for the grand total row and return different results there
- use the Custom SQL technique for duplicating your data to generate your own Grand Total row
- use the two worksheets on a dashboard technique, where you have a separate worksheet for generating the grand totals
Thanks for the response.
I've switched the moving averages to fixed addressing as you've recommended. As far as grand totals for the moving averages, I don't really care what they total to because I won't be their totals for anything (a total of a moving average is worthless really).
I use the MA's for the Revenue Loss column. The calculation of lost revenue is contingent of the load time and add-to-cart rates differing from their moving averages. In the initial view, all of the columns are correct and the only issue is that I need the grand total for the Revenue Loss column to be = to the sum of the above cells (a window_sum). I've tried all of the techniques you mention above (I'd love it if the last one would work as the only number I need for the visualization is the Total Revenue Loss).
I'll continue to fiddle with things, I just wish I didn't have to do so much trial and error when working in Tableau.