Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
1) use conditional formatting for assigning values to the questions.
2) use the below measure.
4) similarly you can do it for question 2 and 3
if this solves your issue please accept my answer as 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
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!