Calculate the question scores based on the number of yes response in sub-questions

Question

I have a table with the survey questions and answers loaded from the SQL database into Power BI.

There are 10 questions in total within the survey. (This number is fixed).

Under each question, there are item lists attached to it, and the item list varies among different surveys completed.

For example, for survey completed with a survey ID A1 (Survey ID to distinguish different surveys completed), there are 5 items asked for Q1, whereas survey ID A2, there are 3 items asked for Q1.

For each item we asked, there are sub-questions to them, and the answer to sub-questions should be either yes or no. I create a scoring system to look at the answers to each sub-question under each item - I will get mark only if my answer to each sub-questions is yes under each item.

The number of sub-questions will be the same under the same question group.

For example, there are always 3 sub-questions under Q1 for each item I asked in each survey, and there are always 2 sub-questions under Q2 for each item I asked in each survey, no matter how many items I ask among different surveys.

Example of surveys data:

| Survey ID | Question | Item | Sub Question | Answer |
|-----------|----------|------|--------------|--------|
| A1        | Q1       | X    | q1           | Yes    |
| A1        | Q1       | X    | q2           | No     |
| A1        | Q1       | X    | q3           | No     |
| A1        | Q1       | Y    | q1           | Yes    |
| A1        | Q1       | Y    | q2           | Yes    |
| A1        | Q1       | Y    | q3           | Yes    |
| A1        | Q2       | X    | q1           | No     |
| A1        | Q2       | X    | q2           | No     |
| A1        | Q2       | Y    | q1           | Yes    |
| A1        | Q2       | Y    | q2           | Yes    |
| A2        | Q1       | X    | q1           | Yes    |
| A2        | Q1       | X    | q2           | Yes    |
| A2        | Q1       | X    | q3           | Yes    |
| A2        | Q2       | Y    | q1           | No     |
| A2        | Q2       | Y    | q2           | Yes    |

Above is an example of the data I loaded from the database.

Edit: There are some questions which do not have the item list attached. The response option to these questions will be either "yes" or "no". To these questions, I will get full mark if the answer is "yes" and 0 if the answer is "no".

enter image description here

Assume Q1 and Q2 both worth 10 marks.

The score for each Survey should be:

Survey A1: For Q1, I get 5 marks. For item X, I get 1 Yes out of 3 sub-questions, therefore it does not count; for item Y, I get 3 yes out of 3 sub-questions, therefore it counts. I get the mark for 1 item out of 2 items, thereby scoring 5 marks out of 10.

Similarly, for Q2, I do not get the mark for item X, but I get the mark for item Y, so I get 5 marks for Q2.

My total score for Survey A1 is therefore 10 marks out of 20 marks.

Survey A2: Based on the same logic, I get 10 marks out of 20 marks.

Could anybody suggest a way to construct the formula/new tables in order to achieve my goal?

My logic is to make a new table based on the current data I load.

Example table

If there is any No's under sub-questions for each item, I assign No to the Check Response column of that item. I assign yes for the check response column only of all sub-questions answers for that item are "yes".

Then, based on the new table, I could directly calculate my score based on the percentage of yes response.

Due to my lack of knowledge of the Power BI language, I do not know how to create a table like this. Could anyone provide an example of code/steps or better solution other than the solution I proposed?

Please let me know if I am clear enough so that I could explain anything in detail if I confuse you.

Thanks in advance!

Alexis Olson Alexis Olson · Accepted Answer · 2019-02-19T21:02:59

You can create a summary table like the one you have like this:

SummaryTable =
    SUMMARIZE(
        Surveys,
        Surveys[Survey ID],
        Surveys[Question],
        Surveys[Item], 
        "MinAnswer", MIN(Surveys[Answer])
    )

Note that MinAnswer will be Yes only when all answers are Yes.

You can use this table within a measure to calculate the percent score on the survey.

PercentAllYes =
VAR SummaryTable =
    SUMMARIZE (
        Surveys,
        Surveys[Survey ID],
        Surveys[Question],
        Surveys[Item],
        "MinAnswer", MIN ( Surveys[Answer] )
    )
RETURN
    AVERAGEX ( SummaryTable, IF ( [MinAnswer] = "Yes", 1, 0 ) )

This will return 50% for both surveys.

Calculate the question scores based on the number of yes response in sub-questions

1 Answers