Forum Discussion

KimberlyHM's avatar
KimberlyHM
Frequent Visitor
4 years ago
Solved

How to calculate/score question answers

Hi guys!

 

I cannot seem to figure this one out, hopefully you can help me:

 

We have a sharepoint list where we collect responses from a form with 20 quesions with Yes/No/NA answers (Yes is a pass and No is a fail, N/A should not be counted). Each row in the query is a seperate form response. We would like to calculate a scoring for each row, the maximum scoring you can get is 100%:

 

We also have loaded a seperate facts table with all the questions and the weighed scoring each questions should have, together it's 100%. But I cannot seem to figure out how to set this up and connect the two. For example if questions 1 is a "yes" then it should be 4% of the total scoring, and so on:

 

My thoughts:

- Convert the Yes to 1, No to 0, N/A to null. This so you can multiply this by the weighed scoring in the facts table

- Ideally I would only want to add 1 column in the first query with the total scoring?

- Otherwise I have to add 20 extra columns for each question with the calculated score, and 1 total to add them up?

 

How would I do this? I need help with calculating the scoring and the steps to do this.

Thank you for the help 🙂

 

Greetings Kimberly

Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Hi KimberlyHM ,

     

    I think the first thing you need to do is unpivot your answers table, so each of the questions is in a single column, the same as your scoring table.

    You now need to create a match on question between the answers and the scores tables. You can either add a conditional column to the answers table, like 'if questionName = "blahblah" then "Q1"... so that you can match Q1 to Q1, Q2 to Q2 etc. or you can adjust the question names in your answers table so that "Communication Skills" = "Communication Skills" etc.

    Now load both tables to the data model and relate the answer table to the scores table either on scores[Index Number] = answers[newQnumberColumn] or scores[Attribute] = answers[newQnameColumn], depending on which method you chose to match the tables.

     

    Once related, you can write measures across the tables for your scoring etc.

    For example:

    _totalScore =
SUMX(
  answersTable,
  (answersTable[Value] = "Yes") * RELATED(scoresTable[Weighted score])
)

     

    Pete

3 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    4 years ago

    Hi KimberlyHM ,

     

    I think the first thing you need to do is unpivot your answers table, so each of the questions is in a single column, the same as your scoring table.

    You now need to create a match on question between the answers and the scores tables. You can either add a conditional column to the answers table, like 'if questionName = "blahblah" then "Q1"... so that you can match Q1 to Q1, Q2 to Q2 etc. or you can adjust the question names in your answers table so that "Communication Skills" = "Communication Skills" etc.

    Now load both tables to the data model and relate the answer table to the scores table either on scores[Index Number] = answers[newQnumberColumn] or scores[Attribute] = answers[newQnameColumn], depending on which method you chose to match the tables.

     

    Once related, you can write measures across the tables for your scoring etc.

    For example:

    _totalScore =
SUMX(
  answersTable,
  (answersTable[Value] = "Yes") * RELATED(scoresTable[Weighted score])
)

     

    Pete

    • KimberlyHM's avatar
      KimberlyHM
      Frequent Visitor
      4 years ago

      Hi Pete,

       

      Thank you for this solution! We had to create some different measures, but in the end it worked 😄

  • jennratten's avatar
    jennratten
    Icon for Super User rankSuper User
    4 years ago

    Hello - This is an example of how this can be accomplished.  Essentially you zip the list of answers with the list of weightings, remove those that don't equal yes and then sum the weights that remain.

    Answers with Score

    Weights Table

     

    SCRIPT

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WikwtVtJR8ssHEiBmrE60kp8jlIMQQvCgYrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Q1 = _t, Q2 = _t, Q3 = _t]),
    TestScore = Table.AddColumn(
        Source, "Score", 
        each List.Sum( 
            List.Transform ( 
                List.Select ( 
                    List.Zip ( 
                        { Weights[Weight], Record.ToList ( _ ) } 
                    ), each List.Contains ( _, "yes", Comparer.OrdinalIgnoreCase ) 
                ), each _{0} 
            ) 
        ), type number 
    )
in
    TestScore