I am having trouble creating a %variance column. Essential I want to display 4 columns: 2014 sales, 2013 sales, variance (2014-2013), and % variance ((2014-2013)/2013).
I have no problem creating the first three columns with 3 calculated fields:
FY2014 : IF [fscl_yr_nb]=2014 then [Sales] ELSE 0 end
FY2013 : IF [fscl_yr_nb]=2013 then [Sales] ELSE 0 end
Variance: [FY2014] - [FY2013]
The problem comes when I try to create % variance. When I use this formula:
[Variance]/[FY13]
I end up with an aggregation of the variances for each day. For example, I end up with -383600%. When I change the formula to:
SUM([Variance])/SUM([FY13])
I get an error message that says 'The calculation 'Sum of % Variance' can't be applied to a user-defined aggregate.
Any ideas how to create this field? So frustrated
Using Sum(Variance)/Sum(FY13) will work, but if you make this change to the calculated field after you put it in your report, you will get the error. You need to make that change first, then add the field to the report.