How to unpivot a multiple choice survey question and visualising % chosen

[Anonymous]

Hi,

I'm new to PowerBI and struggling to figure out how to format my data for a multiple choice survey question.

I have thirteen answers options that a respondent could choose up to 4. I would like to create a ranked bar chart of the options.

I have tried using PowerQuery to unpivot the columns into a single attribute and value, and when plotting this displays the correct count of the values.

However when I want to show the % chosen instead of count by selecting % of grand total, rather than looking at the total number of respondents (510), it is dividing it now by the total number of rows in the unpivoted data set, so the % chosen appears much lower.

How do I get the chart to look at the correct total for the %? My raw data is displayed like below:

Thanks! Sam

[parry2k]

@Anonymous unpivot surely the way to go, but for % you need to calculate distinct count of response id and then calculate the %

[v-frfei-msft]

Hi @Anonymous ,

We can make it divide by the countrows of non blank value for the column value as below.

Measure = 
VAR cr =
    COUNTROWS ( 'Table' )
VAR crr =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[Attribute] <> BLANK () )
    )
RETURN
    DIVIDE ( cr, crr )

For more details, please check the pbix as attached.