3 Replies Latest reply on Jan 18, 2019 1:32 AM by Lewis Wong

    Weighted average score by week

    Andrew Hutchinson



      I’ve got a sheet that needs to show the calculated weighted mean score for customer satisfaction each week - the score is weighted based on the customer group that they belong to. The week is calculated from a date of contact (starting on a Monday) from a date in the format DD/MM/YYYY


      Customers fall into three groups:


      Group 1 – who contribute 0.5 to the overall weighted mean

      Group 2 – who contribute 0.3 to the overall weighted mean

      Group 3 – who contribute 0.2 to the overall weighted mean


      I have three calculated fields to work out the weighted average score:


      A “weighted case statement”:


      CASE [ServiceType (group)]

      When 'Group 1' then .5

      When ‘Group 2' then .3

      When ‘Group 3’ then .2



      This is then used in the next calculated field “Weighted scores”:


      [Killer question] *[Weighted case statement]


      Killer question (is Q31 in the attached changed from a dimension to a measure) is the measure of customer satisfaction rated from 1-10


      The third and final calculated field uses the result from “Weighted scores” and is as follows:


      Sum({FIXED DATEPART('week', [Date Of Contact]),[ServiceType (group)]:AVG([Weighted scores])})


      This has worked perfectly for a number of months, however on updating the raw data today the overall weighted score is showing as 16 – however the customer scores out of 10. I believe that the calculation is perhaps excluding those dates which are from 2019 when averaging however I don’t know if this is the case or how to check/fix this!


      Is anyone able to advise or help on this? I've attached a sample workbook