calculate the % yes response based on the sub-question answers

[Anonymous]

Hi,

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

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.

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.

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?

Thanks in advance!

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

After my test, you could try to use this formula to create a measure:

Result =
VAR _table =
    SUMMARIZE (
        Table1,
        Table1[Survey ID],
        Table1[Question],
        Table1[Item],
        "_marks", IF (
            CALCULATE (
                COUNTA ( Table1[Answer] ),
                FILTER ( Table1, Table1[Answer] = "Yes" )
            )
                = CALCULATE ( COUNTA ( Table1[Answer] ) ),
            1,
            0
        ) * 10
            / CALCULATE (
                DISTINCTCOUNT ( Table1[Item] ),
                ALLEXCEPT ( Table1, Table1[Survey ID], Table1[Question] )
            )
    )
RETURN
    SUMX ( _table, [_marks] )

Result:

and here is pbix file, please tyr it.

Best Regards,
Lin