There are a couple of way to fix this. I'll describe both, but the easiest is to skip the table calcs and use a duplicate data source.
Duplicate Data Source
Just click the current data source > Duplicate. From the secondary data source, drag Historical Days to Arrive to the Measures Values shelf and select AVG.
Since you don't want to calculate this value based on Employer or Location, click the secondary data source and click the orange chain-links next to these dimensions. Or course now if you filter an Employer or Location in the primary data source, your AVG() will not be updated accordingly, ...
See the attached tab "Fixed Using Dup Data Source".
Using Table Calcs
Nothing could be easier than the above, but you found a couple of interesting issues with table calcs that might be helpful to understand down the road.
1. WINDOW_AVG() vs TOTAL()
Window_XXX functions are an aggregate of an aggregate. WINDOW_AVG(AVG(Days to Arrive)) averages all of the average days to arrive across the partition.
TOTAL() is an aggregate at a higher level of detail. TOTAL(AVG(Days to Arrive)) will average all of the [Days to Arrive] values across the partition.
In this case you want TOTAL(AVG(...))
I created a field Calculation2 (copy) that replaces WINDOW_AVG with TOTAL. When you add this to the view, click the pill and select Edit Table Calculation > Compute Using > Advanced and move all of the dimensions, except Product, to the right-hand Addressing side of the dialog box.
2. Filtered Data
It still doesn't work, because you're filtering values where the count of New Orders is 0. For example Employer D -> Location - D1 has no new orders for mugs and, therefore, the historical orders are not in the view and are not included in the Calculation2 (copy) average. You can check this by removing the filter.
You can fix this this with a "table calc filter." Tableau applies most filters when it queries the data source. The big exception is table calculations. Table calculations are done within Tableau and, therefore, filters based on table calculation are done within the view after the other calculations, including other table calculations.
Creating a table calc filter is easy, if not intuitive.
New Orders Table Filter = LOOKUP(COUNT([New Orders]), 0)
LOOKUP() doesn't do anything except turn COUNT(New Orders) into a table calculation. But now you can put this new filed on the filter shelf and set the filter as you did previously.
2b. BUT this breaks (or fixes?) Total Open Orders Calculation
The calculations for New Orders and Open Orders are also done before the table calc filter.
This means that your Open Orders calculation now includes all products with open orders, even if they have no new orders. Check the Total rows for Total Open Orders, and you'll see the sum != the visible values---again the table calc filter is hiding, not excluding, the data.
I'm not sure if this is what you want or not. It doesn't really make sense to have a total row that doesn't equal the sum of the above values. On the other hand, location A2 has more open orders than shown in the table.
Fixing this requires some more work, maybe a using custom total calculations with a conditional. In any case I suspect the duplicate data source approach is easier.
Thanks so much for the response. I was kind of circling around these approaches but could not get things to work the way I wanted. Your detailed reply was just what I needed. Thanks again!
No problem. Thanks for the follow-up.