I have a report that groups by company name. I have two fields that contain financial data and when computing the variance between the individual records (projects) it comes out OK but when I try to derive the variation of the totals for the group (company) I do not get the variation of the totals but something else.
(http://www.nishnorthcentral.org/images/variation_problem.jpg)
The formula I use for the variation of the individual records is abs(1-([REVENUE])/([EXPENSES])). This works on a record level but on at the summary level.
I have also tried to use abs(1-(total([SALES] for [COMPANY])/total([EXPENSES] for [COMPANY]))) for a calculated aggregation.
Any assistance would be appreciated.
Cygnex
Not familiar with your formula for working out the variance, I would have used ([REVENUE]-[EXPENSES])/[REVENUE]*100 (you dont even need the multiplier when formatting using the percent option)
To ensure that the percent is calculated properly at the summary level, you have to ensure that in the properties pane that both the Fact Item Aggregation Function and the Data Item Aggregation Function are both set to Automatic.
The above should give the results you need, let me know how you get on.
Sarge