Forum Discussion

commited2020's avatar
commited2020
Frequent Visitor
3 years ago
Solved

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 accord...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

aha, there is unclosing bracket,

try this:

Result = 
VAR _count =
LEN([Brokers]) - 
LEN(SUBSTITUTE([Brokers], "&", "")) +1
RETURN
DIVIDE([Commission], _count)
commited2020's avatar
commited2020
Frequent Visitor
3 years ago

now  "_count " is underlined red.

i copy and paste the code from here. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      commited2020
      Frequent Visitor
      3 years ago

      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