Adding a column with an incremental count based on multiple selections in another column.

[dparkinson]

That subject doesn't even make sense to me and I'm writing it...

Basically I have a database that has a stupid multi select field in it which means that it can store multiple values and I need to extract them to work out the split between the categories stored in it.

Probably easiest with an example.  Lets say the field looks like the following over a few rows:

,type1,type2,type3,

,type2,

,type3,type4,

,type4,type5,type6,type7

Now lets say that each type belongs to a different category, so:

Category 1 = type1, type3

Category 2 = type2, type5, type6

Category 3 = type4,type7

For each of the categories, I need to split an amount between them.  So, let's say I have $50,000 to share between the categories for row 1.  For the first row, you can see that there are two fields in Category 1 and one field in category 2, so I split the $50,000, with Category 1 getting 66% and Category 2 getting 33% of the amount?  --Does that make sense?

I figured I'd add a calculated column where I can check if the field contains the text for each type and do a +=1 to a count.  I could then use that count across the categories for a total so I can do cash_for_category = available_cash / total_category_entries * number_of_entries_for_this_category, i.e.

Category 1 Cash = (50,000 / 3) * 2

- Where 50,000 is the cash to share

- Where 3 = number of categories in the field

- Where 2 = number of fields in this category

Category 2 Cash = (50,000 / 3) * 1

Hopefully that all makes sense.

I suspect there is a much simpler way to ask this and hopefully a much simpler answer.

Many thanks for any assistance anyone can provide.

Regards,

D.

[itchyeyeballs]

Simple way to count the number of types may be to just do something like

(Len('Table'[Field]) - Len(substitute('Table'[Field]),",","")) + 1

without seeing your table structure its hard to suggest solutions for the rest

[ImkeF · Accepted solution]

Perfectly doable, but not trivial - depending on the exact shape of your input data - have a look at the file: https://www.dropbox.com/s/l4fdvv3ql161p28/PBI_AllocateCategories.pbix?dl=0

[dparkinson]

Apologies for the delay.  I've only just got around to downloading your example.

Thankyou very much indeed.  That was very useful providing the PBIX file -- it allows me to see exactly how you built it up to the final result.  Now I just have to try and replicate that with my data.

Many thanks again for pointing me in the right direction.

Kind regards,

D.

[SNik]

Hi Imkef

thanks for the solution it opend my mind, now i have a different positive look towards M Lang.

thanks

Nik

[ImkeF]

@SNik Thats great & thanks for the feedback!

You can basically do all your Excel-calculations with M.