How to assign a portion $ from one field against string values

[MickK]

Folks, 

I have an excel that contains 2 cells. The first contains a total value $ and the second is a list of who that value is assigned to. In some cases, the assigned is a single dept, in others it can be assigned to multiple depts. In those cases, they list the depts in that single cell separted by a /.

I want to be able to assign an equal portion of the total value against each of the assigned depts and report it within my PowerBI report. What is the best way to approach this?

[rocky09 · Accepted solution]

Here you go. You need to modify the data in the M code. Here I am attaching a sample file for your reference.

https://1drv.ms/u/s!AilGKsSdStq8kTzm2cP2xPlfRdPd?e=wUdHEz

[rocky09 · Accepted solution]

If your issue resovles. Please accept my reply as Solution.

[MickK]

Fantastic. Thank you. 

[rocky09 · Accepted solution]

Here you go. You need to modify the data in the M code. Here I am attaching a sample file for your reference.

https://1drv.ms/u/s!AilGKsSdStq8kTzm2cP2xPlfRdPd?e=wUdHEz

[rocky09]

Some data and the results you are looking for. So, it will be easy to get answered.

[MickK]

The data I see looks like:

I want to report it as:

Basically, for each line item, the value will be aportioned based on the number of the assigned depts.