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.
Will try to check it out today.
Hi Mike,
I/m not sure how I got that screen shot. Unfortunately I didn't save it and lost it when I came in next morning. I'll keep trying and let you know if I succeed in replicating.
Sorry
BR,
Vijay
Hi Sunny,
Hoping you have had an opportunity to review this a little more? Thanks!
Nope, checking it now... should get back to you in 10 mins with questions (if I have any)
Thanks, I appreciate your help very much!
Try this
Avg(TOTAL <Year> Aggr(Sum({$<RecordType={"LOAD"}>} UnitsLoaded),Date))
That expression gives the overall average for all warehouses. I'm looking for the sum of the warehouse averages. In your screenshot, that would be the sum of HUR, SAL, SBF, SSS, YBF, YSS, and YUM. The grand total of the warehouses is 907,342.
Change Avg to Sum
Sum(TOTAL <Year> Aggr(Sum({$<RecordType={"LOAD"}>} UnitsLoaded),Date))
I should have mentioned that I tried Sum instead of Avg in your expression and I got the number for the entire year instead of a daily avg total. The number I'm getting is 39,605,548.
Thanks.
Hi Sunny,
I've tried multiple variations of expressions and still not able to get it to do what I want.
I even tried wrapping the daily average expression (vBoxesLoadedPerDay) in a Sum(Aggr()) and got interesting results but not what I'm looking for. Any other suggestion you can think of?
Thank you!