Hi,
I've got a workbook that has a simple calculation of [Amount Shipped / Total Sales], so it reports what percent of the sales are shipped.
It works fine when I'm just looking at one store, but it doesn't calculate when I look multiple.
StoreShippedSalesCalculation % ShippedStore #15010050%Store #2100100010%Total150110013.6%
So if I select both stores and I have SUM selected for the calculation I get a result of 60% (Store 1 at 10% + Store 2 at 50%) or if I do AVERAGE I get 30%. But I'm looking to see 150 divided by 1100 to get 13.6%
So it's running the sum or average on the calculated field for each store, instead of the total of the underlying data.
Any ideas how to get this to run correctly?
You just need to make sure your calculation matches the aggregation of your fields, by wrapping each side of the % Shipped calculation in SUM().
If you change your formula to: SUM([Shipped])/sum([Sales]), you should return accurate results in the grand total row.
See version 8.0 workbook attached.
