Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
If this is correct, then this should work (I think)
Sum(Aggr(Avg(Aggr(Sum({$<RecordType={"LOAD"}>} UnitsLoaded),Date,Warehouse)), Warehouse))
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!
How about this
Sum(TOTAL <Year>Aggr(Avg(Aggr(Sum({$<RecordType={"LOAD"}>} UnitsLoaded),Date,Warehouse)), Warehouse, Year))
YES!!! That works perfectly. Thank you!!!
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....
Glad to see that finally you achieved closure on this question
I mistakenly thought my row numbers were correct..I appreciate that you stuck with me and figured it out.
Well, I almost lost hopes, it was Vijay who stayed with you vvira1316. All kudos to him
Yes, much appreciate to vvira1316 also, who was very helpful and did an awesome deep dive into the issue.
Thanks to you both!