Scoring survey answers

[Bauhaus-Arti]

Hi Power BI community,

After doing some research i have not been able to solve my problem so i hope you guys can help me out. I have two tables:

survey awswers:

Name	Question 1	Question 2	Question 3
John	yes	no	no
Jane	no	yes	no

Scoring table:

Question	score_scenario_1_yes	score_scenario_1_no	score_scenario_2_yes	score_scenario_2_no
1	4	0	2	6
2	3	1	2	0
3	2	3	8	1

The goal that im trying to achieve is a caluated column that creates a total score per scenario based on the scoring table. The result would look like this:

Name	Question 1	Question 2	Question 3	score_scenario_1	score_scenario_2
John	yes	no	no	8 (4+1+3)	5 (2 + 0 + 3)
Jane	no	yes	no	6 (0+3+3)	9 (6 + 2 + 1)

By this point i have no idea how i can accomplish this sinds there is no relationship between the two tables... I hope any of you are able to help me out. Let me know if you need some extra information.

Thank you for your help.

[Nathaniel_C]

Hi @Bauhaus-Arti ,
If John answered q3 as how is his score for Scenario 2 = 5 (2 + 0 + 3)? Trying to understand.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Bauhaus-Arti]

Hi @Nathaniel_C ,

I made a mistake with choosing the right score, for Jhon it shoud be 2+0+1 = 3. To specify the calculation, there are two scenario's, let's take scenario 1 for exaple.  When a respons is submited with the answers Q1 = yes, Q2 = no and Q3 = yes, the calulation has to lookup the values 4, 1 and 2 in the scoring table and add these in the score_scenario_1 column. The same has to be done for scenario 2 so that for the last step the highest scenario can be chosen.

Hope this clears things up 🙂

[parry2k]

@Bauhaus-Arti solution is attached to give you an idea how it will work, you can enhance/tweak it from here.

[Bauhaus-Arti]

Hi @parry2k ,

Thank you for your respons! 

I can see you have splitted the answers across the rows. Unfortunately im not able to mutate the table that much. We get there answers with microsoft forms and when we export these, the questions will be the column names, just as the survey awswers table. Sinds we have to do this about 50 times a year i am looking for a way that costs minimal effort and can possibly be automated. I had these three steps in mind:

1. load the survey awswers 
2. load the scoringstable
3. add the calculated column 

The idea of the scoringstable is that we have one place where we can edit the scoring if we need to change them. Do you think you can make this work? 

thank you so much for the effort 🙂

[parry2k]

@Bauhaus-Arti unpivot survey answers is done by power query so your raw data will be used as it is, you don't need to do it manually, check query steps in query editor.