
1. Re: Problems with Totals
Jonathan Marquez Dec 13, 2011 8:03 PM (in response to Ian Hutchison)This seems to be a data quality issue.
The problem is that the calculation that you created is also applied at the Grand total level. Thus the aggregate Product Gross Value (221) is divided by the aggregate count of Booking ID (12) and multiplied by the aggregate distinct count of Sales Invoice ID (22).
If is is a oneoff anomaly, you can try this formula
case [SalesInvoiceID]
when 200204397 then 11
else [ProductGrossValue]
end
This will assign a value of 11 (instead of 22) to sales invoice 200204397.

2. Re: Problems with Totals
Joe Mako Dec 13, 2011 11:14 PM (in response to Ian Hutchison)How about this:
1. place "SalesInvoiceID" on the Level of Detail shelf
2. create a calculated field with a formula of:
IF FIRST()==0 THEN WINDOW_SUM(AVG([ProductGrossValue]),0,IIF(FIRST()==0,LAST(),0)) END
3. place this calc field on your text shelf instead of your "Calculation1"
4. set this pill's compute using to "SalesInvoiceID"
Is that what you are looking for?

3. Re: Problems with Totals
Ian Hutchison Dec 14, 2011 3:28 AM (in response to Ian Hutchison)Hi Thanks to both for your help. Joe, your formula works perfect and gives me the result im looking for. Many thanks again. Can you explain to me how this formula works? Im trying to understand it but having difficulty.

4. Re: Problems with Totals
Joe Mako Dec 14, 2011 6:39 AM (in response to Ian Hutchison)This article has a great explanation: