1 Reply Latest reply on Aug 25, 2014 8:11 AM by Seth Gundy

    % variance problem

    Dan Weinbeck

      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