Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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
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!
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
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.
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.
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 ) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |