Percentage of positive answers based on respondents

[NB3]

Hi everyone,

I'm stuck with this data I have based on a study we did.

To put it in a nutshell let's say we have 10 people who answered our questions.

One of the questions had 5 answers for which they had to say Yes/No/Idk

What we want is find the percentage of "YES" for each possible answer to this question.

Each answer is in a column so right now I have a main table with and Id for each person.

And another pivoted table to have all the possible answers to this question in the "Attribute" column

And the answer given for each attribute (Yes/No/Idk) in the "Value" column.

I'd like to know if there is a Dax formula or different steps of Dax formulas to calculate the percentage of Yes for each answer for each person.

Example:

Q1 (x 10 people)

 Proposition 1 - Yes/No/Idk

 Proposition 2 - Yes/No/Idk

 Proposition 3 - Yes/No/Idk

 Proposition 4 - Yes/No/Idk

Thanks everyone for the help,

Cheers,

[v-yiruan-msft]

Hi @NB3 ,

Please try to create a similar measure as below:

percentage=
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( 'table'[id] ),
        FILTER ( 'table', 'table'[value] = "Yes" )
    ),
    DISTINCTCOUNT ( 'table'[id] )
)

If the above measure is not appliable for your scenario, please provide some sample data about question and attribute table and your actual expected result. Thank you.

Best Regards

Rena

[NB3]

@v-yiruan-msft the measure returns "100%" for all of the subcategories of the question,

This what my dataset looks like. 

ID for each person and a,b,c,d,e for each possible answer to question 4 (1=Yes, 2=No, 99=Doesn't know)

Thanks,

[v-yiruan-msft]

Hi @NB3 ,

You can create a slicer with propositions fields, then create a measure to calculate the percentage of positive answer per proposition(just like below screen shot).

percentage of Q4 = 
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( 'Question'[ID] ),
        FILTER ( 'Question', SWITCH(SELECTEDVALUE('Propositions'[Proposition]),"Q4a",'Question'[Q4a], "Q4b",'Question'[Q4b], "Q4c",'Question'[Q4c], "Q4d",'Question'[Q4d],"Q4e",'Question'[Q4e]) = 1 )
    ),
    DISTINCTCOUNT ('Question'[ID] )
)

Best Regards

Rena