80 Replies Latest reply: Nov 6, 2017 1:18 PM by VIJAY VIRA RSS

    Problem summing an average in pivot table

    Mike Grattan

      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.

       

      pivot table average totals by Warehouse.jpg