Clustered bar chart with each cluster adding up to 100%

[Anonymous]

Hello, 

I am working with survey data in powerBI (apologies, cannot share client data), and have a multiselect question. Respondents are asked about how often the partake in different activities. I have the data unpivoted so one colum is the respondent ID, one is the activity they were asked about and the last is their response:

I can then use this to make a clustered bar chart using a measure, with each cluster being the activity asked about:

However, the percentages arent quite right, and im struggling to get them to work, they either seem to be half what they should be, or add to more than 100% for each activity. I adapted a measure that was suggested here https://community.powerbi.com/t5/Desktop/Calculating-Percentage-of-total-column-for-bar-chart/td-p/1... so now have 

Dine out Measure =

DIVIDE (

DISTINCTCOUNT ( 'Dine Out'[uuid] ),

CALCULATE ( COUNT ( 'Dine Out'[uuid] ), ALLEXCEPT ( 'Dine Out', 'Dine Out'[Response] ) )

)

But am new to PBI and not sure if I'm barking up the wrong tree or not. Any help would be greatly appreciated as this is the kind of question I have to work with a lot.

Best wishes!

[v-yulgu-msft]

Hi @Anonymous ,

I think you should use COUNT function to calculate the number of uuid per Dine out per response.

Dine out Measure =
DIVIDE (
COUNT ( 'Dine Out'[uuid] ),
CALCULATE ( COUNT ( 'Dine Out'[uuid] ), ALLEXCEPT ( 'Dine Out', 'Dine Out'[Response] ))
)

Best regards,

Yuliana Gu

[Anonymous]

Hi @v-yulgu-msft,

Thanks for the responses, I actually started with that code and ran into the same problems. The general proportions are right but the actual percents are incorrect and add up to less than 100%. Do you potentially know of any other ways?

Thanks!