Visualizing Multiple Choice Data

[ngiam]

Hi there,

How do I visualize the following multiple choice data:

Respondent Name	What are the ages of your children? (Tick all that applies)
	<2	2-5	6-12	13-18	above 18
B		2-5
C	<2	2-5
D				13-18
E			6-12
F	<2
G		2-5		13-18
H	<2		6-12
I			6-12
J	<2	2-5	6-12
K	<2
L			6-12

Into the following visual (taken from Survey Monkey)

Am quite a basic user when it comes to visualization.

Appreciate your help in advance.

[PhilipTreacy]

hI @ngiam 

Here's a sample PBIX file containing the following steps and visual.

You can unpivot the answer columns in Power Query then do a Group By where those answers are counted.  This gives you this table which is based on the sample of data you provided above.

You can use that to produce this visual

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[DanCh]

50% of respondents in your dataset have children <2

Your visual shows 28,57% because during unpivoting you've got some "extra" respondents

[amitchandak]

@ngiam , if they separated by space. they use split by a delimiter. https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

Or Unpivot : https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[AllisonKennedy · Accepted solution]

I would suggest that you open the data in Power Query, then select the choices for the question and Unpivot data. I've recently done a webinar on handling survey data. See if it might help you with some best practice tips for yours. 

Just click the 'Watch Now!' button under the Survey Says heading: https://www.excelwithallison.com/MCT/news#h.p_NkgcNxVFFYaS