Forum Discussion

enavesca's avatar
enavesca
Frequent Visitor
2 years ago
Solved

Multiple question survey

I have been trying to figure out this simple problem, but I have had no success with that. Any help would be appreciated.

 

I have a survey with multiple questions (Q1, Q2, Q3, Q4, ...). For each question, the respondent can answer with Agree, Disagree, Strongly Agree, or Strongly Disagree. 

This is the Excel data

 

 Q1Q2Q3Q4Q5
Person 1disagreedisagreeagreeagreeagree
Person 2stronglyAgreeagreestronglyAgreestronglyAgreestronglyAgree
Person 3stronglyDisagreedisagreeagreestronglyAgreedisagree
Person 4agreeagreestronglyAgreestronglyAgreestronglyAgree
Person 5agreeagreeagreeagreeagree
Person 6stronglyAgreeagreeagreeagreeagree
Person 7agreedisagreeagreestronglyAgreeagree
Person 8agreedisagreedisagreeagreeagree

 

I need to calculate the percentage of each answer for each question and present the result below:

 Q1Q2Q3Q4Q5
agree50%50%63%50%63%
stronglyAgree25%0%25%50%25%
disagree13%50%13%0%13%
stronglyDisagree13%0%0%0%0%

------------------------------------------------------------------------------------

This is the data pivoted within Power Query (one column for question and another for answer)

 

 

I created the following measure, but it is not working...

 

percentage answer =
    DIVIDE(
        CALCULATE(
            COUNTROWS(TABLE),
            ALLEXCEPT(TABLE, TABLE[field])
        ),
        CALCULATE(
            COUNTROWS(TABLE),
            ALL(TABLE)
        )
    )

 

This is a very common survey, but unfortunately, I am stuck on the calculation. Has anyone had this issue before?

 

Thanks a lot!

survey data