Recognizing comma separated values individually for charts

[gravanoc]

Each row in my data set contains information on an asset that I'm analyzing for performance.  The performance isn't relevant to my question, but it does suggest a correlation between tags that appear in that tool's categories & the tool's relative success.   My goal is to display these tags in a (probably pie) chart where their slice size is influenced by the performance rating.  

Some of the tags appear in categories that are comma separated to indicate multiple results.  For example, in the Product column I might have one record show "Apple", one show "Banana", but another might be "Apple, Banana, Pear".  I experimented with the Text-to-Rows feature in Power Query, and it partially gets the result that I'm looking for, but it throws off some calculations of mine that are inflated by the number of new tools/rows.

In this picture, the left pie is the result from using the data that has not separated the CSVs into individual records, while the right pie has separated them into new rows.

In my opinion, the chart on the right is much better, but it is flawed by the issue I mentioned.  In addition to the new rows/tools created, tags end up being repeated alongside the ones being separated.  How can I ignore those extras and only count the newly separated tags?

[Anonymous · Accepted solution]

Hi @gravanoc ,

You can refer the method in the following link to get it:

Match Multiple Values (Comma Delimited) Power BI

Power BI : Split Comma Separated Values into Filter List

If the above one is not your expected result, please provide more raw data in your table MP921000 (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[sirajshaikh573]

I am facing a similar problem, althoough I understand the above solution works well but how to tackle if the same data has a fourth column that has some revenue or dollar values. In that case it is going to double the revenue numbers.

Please assist