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

# Weighted average score by week

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

• ###### 1. Re: Weighted average score by week

i see that in week 41 there are 2 years, 2018 and 2019.

You may consider including also the year in the view?

Lewis

• ###### 2. Re: Weighted average score by week

Hi Lewis,

Thanks for the suggestion - do you mean like this?

I need to keep the score for week 41 as a single score so I don' think this would work for us.

Andrew

• ###### 3. Re: Weighted average score by week

Yes, but drag year before week.

Are you going to include both years in the score?
If yes, please tell us how to calculate the score when >1 year

Thanks!