Summarize Count of "Agree" "Disagree" "Neutral" in multiple column

[Ratika]

Hi All -

I am working on feedback response data and have data in below fromat.

1. Original Data Format

Question 1	Question 2	Question 3
Agree	Strongly Disagree	Agree
Disagree	Strongly Agree	Strongly Disagree
Neutral	Neutral	Agree
Strongly Agree	Neutral	Strongly Disagree
Strongly Disagree	Agree	Neutral

I want to summarize the above data to show overall likelihood matrix as well as summary data for each question

Something like this:

 2. Achive data in format

	Agree	Disagree	Neutral	Strongly Agree	Strongly Disagree
Question 1	1	1	1	1	1
Question 2	1	0	2	1	1
Question 3	2	0	1	0	2

I can build a visual if I could summarize my original data into data shown above :

Any input will be greatly appreciated.

Thanks in advance

[TomMartens · Accepted solution]

Hey,

first I transformed the dataset a little using Power Query - not DAX.

First I inserted a column that I called "ResponseID", after that the table looked like this:

The 2nd step is unpivoting the columns Question 1, Questions 2, and Question 3, and renamed the columns, after that the table will look like this:

After that it's possible to use the Matrix visual with Questions on rows, Answers on columns, using the ResponseID with the Aggregate formula Count:

Hopefully this approach provides you with some ideas, to tackle your challenge.

This article provide more information on how to analyze survey data: https://www.daxpatterns.com/survey/

Regards,

Tom