Working out %

[Anonymous]

Hi

I have created a measure which calculates the % of people who responded to a question in a survey as below:

= COUNT(Sheet[Respondent ID]) / CALCULATE (COUNT(Sheet[Respondent ID]), ALL(Q7[Q7 Groups]))

I have grouped the data so its only positive or negative responses. There are some respondents who didnt supply a response or said not applicable and as such I want to exclude these when working out the %. How would I change the formula to exclude the blank and irrelvant responses?

The reason I am doing it this way is I want to add a filter so only the positive % is shown but it does not revery back to 100% when the filter is applied.

Thanks

Holly

[v-yuezhe-msft]

@Anonymous,

Please check if the following DAX returns your expected result, if not, please share dummy data of your tables and post desired result based on  the dummy data.

Measure = (CALCULATE(COUNT(Sheet[Respondent ID]),FILTER(Sheet,Sheet[response]<>""))-CALCULATE(COUNT(Sheet[Respondent ID]),FILTER(Sheet,Sheet[response] = "string1"||Sheet[response] ="string2")))/ CALCULATE (COUNT(Sheet[Respondent ID]), ALL(Q7[Q7 Groups]))

Regards,
Lydia

[Anonymous]

Hi

I can’t get that formula to work (I am really new to this and haven’t done anything like it before)

Table: Q7

Rank	Comment	Q7 Group
1	Strongly Agree	Strongly Agree & Agree
2	Agree	Strongly Agree & Agree
3	Disagree	Disagree & Strongly Disagree
4	Strongly Disagree	Disagree & Strongly Disagree

Table: Sheet 1

Respondent ID	Q7 Response
1	Strongly Agree
2	Agree
3	Disagree
4	Strongly Disagree
5	Disagree
6	Strongly Disagree
7
8	Strongly Agree
9	Agree
10	Strongly Agree
11	Agree
12	Agree
13	Agree
14	I don’t use this
15
16	I don’t use this

So what I want to do is work out the % of respondents who strongly agree and agree to Q7 based on only the responses that are agrees or disagrees.

So for this one there are 16 responses

Strongly agree – 3

Agree – 5

Disagree – 2

Strongly Disagree – 2

I don’t use this – 2

Blanks – 2

I want to display the response of

Strongly Agree (3) + Agree (5) / Strongly Agree (3) + Agree (5) + Disagree (2) + Strongly Disagree (2) = % Strongly Agree & Agree (67%).

This ignores the ‘I don’t use this’ and blank responses from the %.   

I then want to display this on a Card visual showing only % Strongly Agree and Agree without it changing to 100% when I remove the other possible responses.

Thanks

Holly

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Create the following measures in your Sheet  table.

Strongly Agree&Agree = CALCULATE(COUNT(Sheet[Respondent ID]),FILTER(Sheet,Sheet[Q7 Response ]<>"" && (Sheet[Q7 Response ]="Strongly Agree" || Sheet[Q7 Response ]="Agree")))

alleffectiveresponse = CALCULATE (COUNT(Sheet[Respondent ID]),FILTER(Sheet,Sheet[Q7 Response ]<>""),ALL(Q7[Q7 Group]))-CALCULATE (COUNT(Sheet[Respondent ID]),FILTER(Sheet,Sheet[Q7 Response ]="I don’t use this"),ALL(Q7[Q7 Group]))

Measure = [Strongly Agree&Agree]/[alleffectiveresponse]

Regards,
Lydia

[Anonymous]

Thank you

[v-yuezhe-msft]

@Anonymous,

Could you please accept my reply as answer? That way, other community members would easily find the answer when they get same issues.

Regards,
Lydia