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

commited2020's avatar
commited2020
Frequent Visitor
3 years ago

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

Token comma expected and "RETURN" is underlined red.

What should i do?

  • 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