Forum Discussion
Working out %
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
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
5 Replies
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,
LydiaHi
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
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