Measure for Column with Text String

[nryalls]

I need help figuring out this problem. I have a column of data where survey respondents choose from a list of 7 items all that apply. I was wondering if there was a measure I could use to return a percentage of those who selected one of those items. However, I can only get results for those who only selected that item, not those who selected it along with others. 

Here is a sample of what some cells might read:  

Colonial Williamsburg Hotel,Evening Programs,Historic Buildings,Historic Landscapes and Gardens,Historic Shop (retail),Historic Tavern (dining)

Colonial Williamsburg Hotel,Evening Programs,Historic Buildings,Historic Landscapes and Gardens,Historic Shop (retail)

Evening Programs,Historic Buildings,Historic Tavern (dining),Historic Trade Shop

I want to know how many total people visited Evening Programs against the total number of respondents. Is there a formula for a measure for this? Or what would be the best way to break this down to show % of people at each activity? 

[AlB]

Hi @nryalls 

It is not quite clear to me what you are need. Could you provide an example based on sample data?

Please show your sample data in text-tabular format in addition to (or instead of) the screen captures. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

Cheers

[nryalls]

Here is a link to the file, I've removed the confidential information. But you will see the Q1 column has large text strings, and this is the column I'm looking to see to count all those who visited Evening Programs exclusive and inclusive of whatever else they selected. I'd like to then be able to see the NPS based on those who visited Evening Programs. Does this make sense? 

Thanks for your help! 

Power BI Test

[AlB]

@nryalls 

Like I said, I need an example based on the data where you show the expected result and how you get there.  I still don't understand what you need.

Cheers 

[nryalls]

Thanks for your patience with this, I'm very new to Power BI. 

But I would ideally like to be able to create a bar graph or simple table visualization that gives me the following: 

Experience                                    % of Guests

The Art Museums                         30%

Colonial Williamsburg Hotel        21%

Evening Programs                        35%

Historic Buildings                         87%

Historic Trade Shop                     67%

etc...

I'm not certain how to get there, but with our survey tool, I am able to break down how many guests visited each experience. Whereas in Power BI, I simply get a list of all possible combinations of guest responses to the question. This is because the question allows multiple answers and the data pull has them delineated by commas. 

[Ashish_Mathur · Accepted solution]

Hi,

In the Query Editor, right click on the Q1 column and select Split > By delimiter > Delimiter should be , > Advanced > By rows.  Click on Close and Apply.  Drag thr Q1 column to the visual and write this measure

=DISTINCTCOUNT(Data[ResponseID])

Hope this helps.

[AlB]

@nryalls 

To obtain the percentages you can, after doing what @Ashish_Mathur suggests, use this measure on the table/matrix visual:

Measure =
DIVIDE (
    COUNT ( Table1[Q1] ),
    CALCULATE ( Table1[ResponseID], ALL ( Table1 ) )
)