Need Help Formatting Vizulaizaitons Using Mutiple Response Survey Data as Percentage of Respondents

[Anonymous]

Hi Everyone!

I just started working with Power BI last week, so apologies if what I'm about to ask is super basic.

I have some survey data that have a number of questions of the "select all that apply" variety. This means that each possible response to those questions appears in the data as a separate column (value = 1 if option is selected, value = 0 if option is not selected). Ultimately what I would like to do is be able to create bar charts summarizing each of these variables individually, but to also have them interact with one another and filter each other based on the response selected.

In the example below, I'd like to create two bar charts, one summarizing the ownership of each business (white, women, black, etc.) and one summarizing the region of operation (urban, suburban, online, etc.). And when I click on say the women-owned bar, the region figure would automatically highlight the distribution of women-owned businesses by region.

Simplified Example of Original Data:

ID	White-owned	Women-Owned	Black-Owned	Urban	Suburban	Online
1	1	1	0	0	1	0
2	0	1	0	1	0	1
3	0	0	1	0	1	1

I've had some luck creating separate sheets of data with each question (Region or Group) unpivoted and filtered so that only checked responses (value = 1) are included, and then creating a many to many relationship between the sheets based on the respondent ID. I'm able to create bar charts for each of the different multiple response questions and these bar charts do indeed filter one another when different responses are selected. This is what it looks like:

This is almost exactly what I want. The only thing holding me back now is that the bar charts are displaying the frequency of each response, and I want to have them displayed as a percentage of all responses.

I've tried the "show value as percent of grand total" option, but since respondents can select more than one option (and therefore can show up more than once in the transformed data) I'm getting skewed percentages for that. I can get the figures formatted as percentages if I change the summation to average and the format to percentage and do not filter the data on only checked responses (so I include the 0's as well). This gets me what I need, BUT I lose the interactive filtering functionality that I originally had. Is there any way to bridge these two methods?

Hopefully this makes sense. Any suggestions you can provide would be much appreciated!

Thanks

[v-lili6-msft]

hi  @Anonymous 

It seems that it could not achieve in power bi for now.

And you may create two sets of bar visuals as  a workround.

Regards,

Lin