Multiple response survey question: How to calculate total number/ % of respondents who selected all?

[deepta]

Hello lovely people, 

A quick doubt to which I am sure there is an easy solution somewhere. 

So, I have a knowledge measurement question and 7 checkbox options to choose. We cannot include the 'I know all' choice as the respondents are/could be young people and there is a possibility that they will tick it without understanding the meaning/because it confuses them. Either way, it is ridden on assumptions and solid experience from previous surveys. 

Now, I am able to see the aggregate numbers in my analysis. Such as, How many selected one option? Overall, which option is the most selected etc? But what I am also keen on knowing is the # or % of respondents who selected all/some of the options. 

To give more context, 

there is a question on good and bad behaviours, and I want to be able to see how many selected all good behaviours, all bad behaviours, (or variations of these conditions). 

What is the best way to figure this out on PBI? The PBI is automated to an MS form survey, so I am ideally looking for ways for which this can be automated as new responses keep coming. 

Sorry for all the waffling. Give me a shout if I can give more context/info on this challenge. Thanks! 

[deepta]

Hello @Greg_Deckler , 

Thank You for your response! Apologies for the lack of clarity.  

1. Sample data as text, use the table tool in the editing bar

ID	Choose healthy and unhealthy behaviours from the list
1	A;B;E;
2	A;C;E;
3	B;D;
4	A;C;E;
5	A;E;

2. Expected output from sample data

Healthy behaviours are A, C, E and unhealthy are B, D 

How can I analyse and present this on PBI dashboard:

The number of students who selected all healthy (to which, answer is 2/5) and all unhealthy behaviours (1/5)? 

Please lmk if I should provide more info. 

If this is not the right group to discuss this issue, could you guide me to the appropriate group? 

[v-rongtiep-msft · Accepted solution]

Hi @deepta ,

I have created a simple sample, please refer to my pbix file.

Create a new table and put the option field into the slicer.

Then create 2 measures.

Measure = VAR _1=SELECTEDVALUE('Table (2)'[healthy behaviors])

VAR _CONNRCT=CONCATENATEX(VALUES('Table (2)'[healthy behaviors]),'Table (2)'[healthy behaviors], ";")

var _not=CONTAINSSTRING(MAX('Table'[Choose healthy and unhealthy behaviours from the list ]),_CONNRCT)


RETURN _not

Measure 2 = VAR _ZI=CALCULATE(COUNT('Table'[ID]),FILTER(ALL('Table'),[Measure]=TRUE()))
VAR _COUNT=CALCULATE(COUNTROWS('Table'),ALL('Table'))
RETURN
_ZI/_COUNT

So is the unhealthy behaviours .

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[Greg_Deckler]

@deepta Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.