Summing values of comma separated instances

[Anonymous]

I am trying to sum the number of instances in a comma separated list. For example, I am looking at website data and want to know the number of hits on each page type tagged to multiple criteria. 

Type Number
News, Sport, Business 20
News 6
Business, Sport 15
Sport, News 12
Sport 7

Desired result Number
News 38
Business 35
Sport 54

I am struggling to create the calculation that produces the breakdown for each type (see desired result). 

I have tried using column "split", so that the type becomes 3 columns Type.1, Type.2 and Type.3 and then get stuck. 

Can someone point me the way to deal with this issue?

[v-danhe-msft]

Hi @Anonymous,

Based on my test, you could refer to below steps:

1.Sample data (I have splited the "Type" column to 3 columns)

2.Unpivot the three type columns.

3.After unpivot remove the Attribute column.

4.Group by the [Value] column and you could get the correct result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/f6k1jmqlntxqj7e/Summing%20values%20of%20comma%20separated%20instances.pbix?dl=0

Regards,

Daniel He

[Anonymous]

Thanks @v-danhe-msft @jthomson for helpful replies. This partly resolves the problem. However, this exposes an issue! My dataset is actually more complicated as I have an additional column (and more in reality) called "Name". I need to be able to see the overall picture of "Number by Type" which this does. But when I consider this additonal column, the method creates an additional row for each delimited value causing issues on overall view where I see 3x Article A value, 2x Articles C and D value. 

I have not grouped any data as I am not sure how this exactly works. 

Essentially if I just filter by single layer i.e. Sport this works perfectly, but if I want to see the overall picture it doesn't.

Filtered view okOverall contains duplication

My desired overall result would be to some see both views including an overview akin to the original view when no filters applied and total as achieved for filtered views. 

Any ideas how this can be achieved? I attach my PowerBI sample file TIA 🙂

[jthomson]

When you say you got stuck, that's a bit vague - are you at a stage like this:

If so, pick the column with your numbers and select unpivot other columns. You may then need to trim your descriptor column depending on your exact data type, but once you've done that you can do what you need to do