2 Replies Latest reply on Aug 10, 2018 7:38 AM by Andrew Hutchinson

    Weighted average score total (graph)

    Andrew Hutchinson



      I've created a weighted score from a set of service types - service type 1, 2, 3, 4, 5 and 6 and people are asked to rate their satisfaction with the service they receive for this out of 10. The data is set up as follows:


      Service typeScore


      The service types form part of a wider group - so service types 1,2,3 all fit into the category of "washing", service types 4 and 5 are "drying" and service type 6 is "ironing" each combined service type group contributes to an overall mean score - however each group has a different weight when it comes to the make up of that mean score. So - washing counts towards 50% of the average score, drying 30% and ironing 20%.


      With some help from the forum I have created this weighted score using a weighting case statement:


      CASE [Service received]


      When 'Washing' then .5

      When 'Drying' then .3

      When 'Ironing' then .2



      From there I have then created a calculated field to apply it to the overall satisfaction score: (([Score for service])*([weighting case statement]))



      So then I have scores that show the weighted score for each area e.g. Washing 4.393, Ironing 1.450, Drying 2.250


      I need to be able to create a total score (which is fine if I want to display in a table - I just use the analysis "Show column total" and "Total using Sum"


      However I need to also create a graph of just the overall average - so in this case - 4.393+1.450+2.250 = 8.093, as the data is collected weekly and we want to track the scores over time and create a trend graph. I've tried this on sheet 2 of the attached workbook but it just displays the individual scores on the graph.


      I feel like it should be quite straightforward to do but I can't think of how to do it! I've attached the example workbook.