Problem summing an average in pivot table

[mikegrattan]

I have a pivot table with a measure that calculates the average number of boxes shipped per day by each warehouse.  I'd like to get a sum of those averages by warehouse so I can then do a percentage of total calculation.  The current expression to get average of boxes per day is:

Avg(Aggr(Sum({$<RecordType={"LOAD"}>} UnitsLoaded),Date))

I've tried modifying that expression in order to get the total for all warehouses but haven't been able to get any variations to work as I expect.

Avg(Aggr(Sum({$<RecordType={"LOAD"},Warehouse=>} UnitsLoaded),Date))

Avg(Aggr(Sum({1<RecordType={"LOAD"}>} UnitsLoaded),Date))

Sum(Aggr(Avg({$<RecordType={"LOAD"}>} UnitsLoaded),Date))

etc.

[sunny_talwar]

May be try this

Sum(TOTAL <Year>Aggr(Sum({$<RecordType={"LOAD"}>} UnitsLoaded), PFWarehouse, Year, Date))

[mikegrattan]

Blank result. 

Please note that PFWarehouse is a Master item expression.  I'm using "Warehouse" in my calculations; PFWarehouse is meant to evaluate to Null if the warehouse is not in a particular match list.  So I updated your suggested formula to use Warehouse instead but got a blank result.

[sunny_talwar]

How about this then

Sum(TOTAL <Year>Aggr(Sum({$<RecordType={"LOAD"}>} UnitsLoaded), Warehouse, Year, Date))

[mikegrattan]

Yes, I did try that and that's when I got the blank result.

[sunny_talwar]

Would you be able to share a sample?

[mikegrattan]

I've attached the QVF file. Please see the "Boxes" sheet and the pivot table called "Boxes Loaded Per Day (Avg).  The pivot table is a Master Item called Boxes Loaded Per Day - Pivot.

The expression for Boxes Per day is stored in a variable called vBoxesLoadedPerDay

I also created a variable for the total of all warehouses called vBoxesLoadedPerDay_AllWarehouses

Thanks.

[sunny_talwar]

Don't see Boxes sheet... am I missing something obvious

[mikegrattan]

Probably attached an out-dated file.  I'll delete the attachment and post again in a few minutes.

[cristinapo]

Hi, doesn't the total number of boxes loaded help, as below? After all, you do want the sum of all boxes loaded, regardless of PFWarehouse, Year, Date

Sum({$<RecordType={"LOAD"}>} TOTAL <PFWarehouse, Year, Date> UnitsLoaded)