3 Replies Latest reply on Feb 29, 2016 5:52 AM by Tharashasank Davuluru

    How to calculate sum of average based on a dimension?

    Ahmed Tawakol

      Hi Everyone,

      I'm looking for some help in creating a calculation that basically sums two or more averages based on grouping by a dimension. I have a workbook that has a weekly target per employee based on tenure. I have created a calculated field:

       

      UNIFIED WEEKLY TARGET =

       

      if [monthsSinceSales] <1 then 0

      elseif [monthsSinceSales] <6 then 5

      elseif [monthsSinceSales] <12 then 5

      elseif [monthsSinceSales] <24 then 3

      else 3

      END

       

      when plotting the data on a crosstab, EMP is a calculation that evaluates to : avg([UNIFIED WEEKLY TARGET]) which is the same as TARGET/# of records

      How can I get the average per region to show the correct number, i.e. in the case of Calgary on 2/8/16 to evaluate to: (130/26) + (603/201) = 8? I would like to create another calculated field that shows this value.

      Right now, if I remove the name field, the average computes to (130+603) / (26+201) = 3.229 which is incorrect. I'm thinking table calculations but can't figure out how to achieve the result I'm looking for.

       

       

      Any assistance would be greatly appreciated!

       

      Thanks