Forum Discussion
splitting between brokers
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 |
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
6 Replies
hi commited2020
try to add a column with this:
Result =VAR _count =LEN([Brokers]) -LEN(SUBSTITUTE([Brokers], "&", "") +1RETURNDIVIDE([Commission], _count)Firsto of all thank you i am new to power bi.
Token comma expected and "RETURN" is underlined red.
What should i do?
aha, there is unclosing bracket,
try this:
Result =VAR _count =LEN([Brokers]) -LEN(SUBSTITUTE([Brokers], "&", "")) +1RETURNDIVIDE([Commission], _count)now "_count " is underlined red.
i copy and paste the code from here.
