2 Replies Latest reply on Dec 19, 2013 11:12 AM by Darin Friedrichs

Running calculation on total selection

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 % Shipped
Store #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?

• 1. Re: Running calculation on total selection

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.

• 2. Re: Running calculation on total selection

Thanks Matthew! I remember seeing this in one of the training videos but had totally forgot. This works great now!