Bar Chart not Representing Full Source Data Set

[Anonymous]

Hi All,

I'm a very new user to Power BI and just getting to grips with it recently. I have completed a survey through work with some customers and wish to use Power BI to analyse the results. One of the questions in the survey pertains to which project types that customers have been involved in with us and is a multiple choice question (more than one answer allowed). Whether a surveyee ticks 1, 2, 3 or 4 boxes, Microsoft Forms inserts all answers into one Excel cell, with each of the options separated by a semi colon. 

As expected, different people have different answers with some customers only being involved in one project and some involved in up to 4. Within Power BI I want to plot total quantities of customers involved in every project type on a bar or pie chart. I have tried to manipulate the data in Power BI to split the single column of answers into a max of 4 columns per surveyee (depending on the quantity of project types they clicked) and then plotting all of these columns in the Values field on a bar chart using the first column of project types as the Axis value. If I plot only the first column in the Value field, the data is correct when I compare what's represented on the bar chart to the raw data table. However when I add the second column for customers who have been involved in 2 project types, additional data is shown on the bar chart but it does not seem to show all of the new values, only a subset (I have set the columns to Count).

I can't see any logic to why certain values are shown or omitted and can't understand why the bar chart works with one column but then doesn't show the correct data when expanded even though the columns are of the same data type. Any help would be appreciated. Thanks.

[v-rzhou-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[parry2k]

@Anonymous can you put sample data in a pbix file and share it, will be much easier to work with, just make some fake data.

[Anonymous]

Thanks. I have generated a sample data set but I don't see an option for me to attach a file to cmy response. I am able to attach photos and videos but no option fore standard files. Maybe it's because my account is new.

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

If you want to show the count of answers, you can try split columns and unpivot columns.

My Sample:

Duplicate Data column as Data-Copy, and split column by ";".

Select three new columns and use unpivot column.

Finally remove Attribute column and build your bar chart.

If this reply still couldn't help you solve your problem, please provide me with your sample by Onedrive for Business. You can show me a screenshot of the result you want as well.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Sorry for the delayed response. This looks like it will work. I'll try it with my current data set. Thanks again.

[parry2k]

@Anonymous you provided great details but it will be easier if you put some sample data/screenshots and tell what is not working and what you are expecting.

[Anonymous]

Hi there,

Below is a snip of some of the data I'd like to plot.

As you can see, the breakdown of answers is as per below:

Email to NTQ Engineer - 5

IM to NTQ Engineer - 2

Meetings & Review - 1

JIRA/Confluence Issue Tracker - 1

Call - 1

However, when I try to plot this data on a bar chart as is, effectively there are 4 rows with unique sets of data in them. I have tried splitting the column into as many columns as is needed to accomodate multiple answers and then plotted all of these resulting columns with single entries onto a stacked bar chart.

Below is an example of this using a different question from the survey but having the same scenario. Surveyees input up to 4 answers to the question so I've split the answer column into 4 distinct columns. I then plotted all 4 columns as the Values in the bar chart but the accumulated totals in the raw data table don't match the actual number of entries when I manually add up the entries of a certain answer across the 4 columns. For example, the first column according to the bar chart shows a total of 40 but the raw data adds up to only 31 entries with 24 in column 1, 7 in column 2 and none in column 3 and 4. I'm very confused by the whole thing

(Apologies I had to censor the text as it is confidential.)