Calculating Weighted Average of (Text) Survey Answers

[hgmc]

Hello,

Short of making dozens and dozens of small formulas is there an effective way to calculate a weighted average for each question in survey data. I want to use these weighted average answers as sources for various graphs in Power Bi.

Sample Data:

It is a survey format so there may be multiple responses for a particular candidate, in this case BOB had 3, JANE had 2. (I'll be using a slicer to filter by candidate name)

Answers are text format and I need to assign them weighting in the formula, For exmaple, Excellent = 3 points, Average = 2 points, Poor = 1 point

I need to calculate the weighted average for each candidate, for each question.

Spent so much time trying different formulas and browsing on this forum but can't seem to find a similar situation. Any help is much appreciated! (ChatGPT actually got me halfway there!)

Candidate Name	Question 1	Question 2	Question 3
BOB	Excellent	Average	Average
BOB	Average	Poor	Poor
BOB	Poor	Excellent	Excellent
JANE	Excellent	Excellent	Poor
JANE	Average	Average	Excellent

[rautaniket0077]

1) use conditional formatting for assigning values to the questions.

2) use the below measure.

Measure =

var a =        

 SWITCH(

            TRUE(),

            MIN(Sheet1[ Question 1 ]) = "Excellent", 3,

            MIN(Sheet1[ Question 1 ]) = "Average",2,

            MIN(Sheet1[ Question 1 ]) = "Poor",1

)  

Var b = CALCULATE(

            SUM(Sheet1[Question 1 No]),

            ALLSELECTED(Sheet1)

)

return (a/b) * a

3) you will get your desired output

4) similarly you can do it for question 2 and 3

if this solves your issue please accept my answer as solution.

[FreemanZ · Accepted solution]

hi @hgmc 

the point is to unpivot your data and add a custom column into a table like this:

CandidateName Question Answer Point

BOB Question1 Excellent 1

BOB Question2 Average 2

BOB Question3 Average 2

...

then everything becomes intuitive with DAX. 

About unpivot:

https://learn.microsoft.com/en-us/power-query/unpivot-column

[hgmc]

Thank-you, looked a bit daunting at first to unpivot as I didnt really understand what I was doing but it did work in the end!