splitting between brokers

[commited2020]

Hello.

I have a sales data of brokers . Some sales are done by 2 or 3 brokers which get split among themselves 50-50 or 1/3 accordingly.

I want to split commission depending on the situation according and also i want to count the sales individually

to give you an example:
When a broker closes a deal i want to count that as 1 point
and if they closed a deal in collaboration with another broker each broker will split the point accordingly,
if they are two sales rep 0,5 points each
if they are 3 0,33 each.

Thank you

Data set example

ACCOUNT	BROKERS	COMMISSION
KYC	KEVIN	$10000
BUNGIE	KEVIN & JOHN	$24000
BUNGIE	BILL & KEVIN & JOHN	$15000
ABC	BILL & JOHN	$12000

[commited2020]

Firsto of all thank you i am new to power bi.

Token comma expected and "RETURN" is underlined red.

What should i do?

[FreemanZ]

aha, there is unclosing bracket,

try this:

Result = 

VAR _count =

LEN([Brokers]) - 

LEN(SUBSTITUTE([Brokers], "&", "")) +1

RETURN

DIVIDE([Commission], _count)

@commited2020 

[commited2020]

now  "_count " is underlined red.

i copy and paste the code from here. 

[Anonymous · Accepted solution]

HI @commited2020,

You can try to use the following calculated column formula if it suitable for your scenario:

formula =
[COMMISSION] / PATHLENGTH ( SUBSTITUTE ( [BROKERS], "&", "|" ) )

Regards,

Xiaoxin Sheng

[commited2020]

Thank u very much but how do i assign the results to each broker accordingly?

For example Kevin and John shoul each get 12.000 and i want to be able to count the whole amount for each one of them

[FreemanZ]

hi @commited2020 

try to add a column with this:

Result = 

VAR _count =

LEN([Brokers]) - 

LEN(SUBSTITUTE([Brokers], "&", "") +1

RETURN

DIVIDE([Commission], _count)