CODEWITHSUNDEEP
powerbi
paradroid
paradroid

Reputation: 27

Power BI: percentage of multiple response question

I presume this is really simple, but I cannot get my head around it. I was to show data the percentages from multiple response questions from a survey.

The original survey data looks like this:

original data

The variables starting with "Own:" are multiple responses, i.e. the first persons owns a car ("yes") and a donkey ("yes"), but no bicycle ("no").

I want to show the answers in Power BI in a chart like this, where I can the the percentage of respondents, who own a certain thing: chart

I have already unpivoted the original data and it looks like this:

unpivoted

Now, I would like to create a measure that counts the number of "yes" per category. Is there any way to do a conditional count (count only "yes") - or transform all "yes" into 1 and "no" into 0 within Power BI?

If I had this measure, I could "simply" divide the number of mentions by the total number of respondents.

Does anybody know how to do this?

Best, para

Upvotes: 1

Views: 887

Answers (1)

Peter
Peter

Reputation: 12375

Use this measure:

% Yes = 
VAR yes = CALCULATE(
    COUNTROWS('Table'),
    'Table'[Value] = "yes"
)
VAR respondents = 
    DISTINCTCOUNT('Table'[ID])
RETURN
    DIVIDE(yes, respondents)

enter image description here

Alternatively derive a numerical Yes column from Value

Yes = IF('Table'[Value] = "yes", 1, 0)

and then use

% Yes 2 = 
DIVIDE(
    SUM('Table'[Yes]),
    DISTINCTCOUNT('Table'[ID])
)

Upvotes: 1

Related Questions