Bar chart where the X axis is a list of possible responses that are within a string of text

[Mpayne1995]

Hi All,

i have a data set of customer feedback data. A customer can give feedback on their experience by 'Department' (2 option closed field where only one option can be chosen), refering to a certain 'Reason For Contact' (60 option closed field where only one option can be chosen), rate their 'Score' 0-10 based on their experience (11 option closed field where only one option can be chosen) and give a 'Justification' for their score (a 7 option closed field where multiple options can be chosen. Responses are comma delimited).

when giving the 'Justification' for their score, they could choose multiple options, like so:

Communication,Engineer/workers,Site visit,Resolution of queries/issues
I have the complete list of possible options for this field. I want to be able to created a bar chart where the Y axis is the average score and the X axis is each of the 7 options, so essentially the plot of the bar chart will show the average score for a response that contains each option. I then want to be able to use a slicer for 'Department', 'Reason for Contact' and other fields i haven't mentioned.

Is this actually possible? 

Thank you for your help

[helassal · Accepted solution]

@Mpayne1995 ,

Yea, this is possible. From the information you provided, it looks like initially once you read the data, you will have a list of columns with single value in each record and then other columns like "Justification" which contain one or more comma delimited values.

Once you read the data in Power BI in Power Query, you will need to split the "Justification" Column by comma into rows

Then you need to add a dummy column with 1 as a constant value (called custom in the example)

And finally, you need to pivot the split column (Justification) based on the dummy column to get a flattened structure.

Now you will have 7 Justification columns, with the value 1 corresponding to being mentioned in the feedback and null meaning not mentioned. You can replace null by 0 and create all sort of charts and filters on the data.

The key is prepare the data first using Power Query.

Hope this helps.

Best Regards,

[Mpayne1995]

Hi There,

Thank you for taking the time to respond to this. Will this not skew the average, as a response with more than one 'Justification' would have the score repeated?

Many thanks,

Michael