Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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[Q...
  • Anonymous's avatar
    Anonymous
    8 years ago

    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's avatar
Anonymous
Not applicable
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      Thank you

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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