Hi

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

End

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

Thanks

Andrew