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.

[mikegrattan]

You are correct.  I've updated the daily average boxes formula to include Warehouse and it now evaluates correctly when comparing to specific dates/warehouses.

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

[sunny_talwar]

If this is correct, then this should work (I think)

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

[mikegrattan]

That expression gives me an accurate total in the Totals line.  My goal is to be able to have a 'percent of total' column in the pivot table as well.  Normally this should be as easy as Column(1) / Column(2) or something similar, but since the total for all warehouses only shows in the Totals line I don't think that would work.

Is there a way to fine-tune your expression so that the total 204,926 shows up on each row?  I tried adding "TOTAL" in various places of the expression but that did not have the correct result.

Thanks!

[sunny_talwar · Accepted solution]

How about this

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

[mikegrattan]

YES!!!  That works perfectly.  Thank you!!!

[sunny_talwar]

Super

I tested this a while ago, but didn't give this because like I said, I thought your row numbers are the way you would like them to be....

[vvira1316]

Glad to see that finally you achieved closure on this question

[mikegrattan]

I mistakenly thought my row numbers were correct..I appreciate that you stuck with me and figured it out.

[sunny_talwar]

Well, I almost lost hopes, it was Vijay who stayed with you vvira1316‌. All kudos to him

[mikegrattan]

Yes, much appreciate to vvira1316 also, who was very helpful and did an awesome deep dive into the issue.

Thanks to you both!