Forum Discussion

PowreNoob2's avatar
PowreNoob2
Frequent Visitor
4 years ago
Solved

Give Text Values Weights

I'm collecting data from a Microsoft Form where the users are selecting Strongly Agree, Agree, Neutral, Disagree or Strongly Disagree for the responses for 50 questions. I want to give the text values weights and score sections based on the responses: 

  • Strongly Agree = 5
  • Agree = 4
  • Neutral =3
  • Disagree = 2
  • Strongly Disagree = 1

I've seen where you can create a conditional column with the value mapping for each question but, is there a more efficient way to do this. For instance if I want to create a column with the sum of Question 1 - 5 is there a DAX method to use like:

(Q1 + Q2 +Q3 + Q4 + Q5) where (Strongly Agree = 5, Agree = 4, Neutral =3, Disagree = 2, Strongly Disagree = 1)

 

ResponderQ1Q2Q3Q4Q5QSUM
1Strongly AgreeDisagreeStrongly AgreeAgreeAgree20
2AgreeNeutralStrongly AgreeNeutralDisagree17
3NeutralNeutralAgreeDisagreeAgree16
4NeutralStrongly AgreeDisagreeStrongly AgreeDisagree17
5AgreeDisagreeNeutralStrongly DisagreeAgree14
6DisagreeAgreeStrongly DisagreeNeutralStrongly Disagree11
Need Help
Tips and Tricks
  • ddpl's avatar
    ddpl
    4 years ago

    PowreNoob2 

    First you need to create one more table as per below:

    Then from Power query merge both the table for each column (Q1 to Q5) and extract teh Rate.

     

    And select all 5 new column (Q1 rate to Q5 rate) and add as per below (You can remove unecessary column)

     

     

     

     

1 Reply

  • ddpl's avatar
    ddpl
    Solution Sage
    4 years ago

    PowreNoob2 

    First you need to create one more table as per below:

    Then from Power query merge both the table for each column (Q1 to Q5) and extract teh Rate.

     

    And select all 5 new column (Q1 rate to Q5 rate) and add as per below (You can remove unecessary column)