Sum # Responses and grouping them by Category

[daidominguez]

Hi all!

I'm trying to sum the number of responses obtained from a survey & organising them in 3 categories.
In this case, I’ve got my 2 main columns:

 - Column "Answer": with 5 values (Highly Agree; Agree; Neutral; Disagree; and Highly Disagree). 
-  Column "#Responses": with the number of responses per category.

 
What I need to do is:
- Sum the number of responses by Category but grouping them together (using different columns or measures) as follows:
      * Positive: for Highly Agree and Agree responses
      * Neutral: for Neutral responses
      * Negative: for Disagree and Highly Disagree responses.

What's the best and easy way to do it? I'd appreciate your suggestions.

Thank you in advance for your help!

[v-lionel-msft · Accepted solution]

Hi @daidominguez ,

Please create calculated column like this.

Sum the number of responses by Category = 
CALCULATE(
    SUM(Sheet1[#Responses]),
    ALLEXCEPT( Sheet1, Sheet1[Answer] )
) 

grouping them together = 
SWITCH(
    [Answer],
    "Highly Agree", "Positive",
    "Agree", "Positive",
    "Neutral responses", "Neutral",
    "Disagree", "Negative",
    "Highly Disagree", "Negative"
)

Or measure like this.

Sum the number of responses by Category = 
CALCULATE(
    SUM(Sheet1[#Responses]),
    ALLEXCEPT( Sheet1, Sheet1[Answer] )
) 

grouping them together = 
SWITCH(
    MAX([Answer]),
    "Highly Agree", "Positive",
    "Agree", "Positive",
    "Neutral responses", "Neutral",
    "Disagree", "Negative",
    "Highly Disagree", "Negative"
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.