Forum Discussion

Kosh's avatar
Kosh
Frequent Visitor
1 year ago
Solved

Calcucate Commission from a Range Table

Need help to add a calculated column for sales commission in each row of the Sales table. The commission % is determined by the total YTD revenue for the sales person for which the lookup range is in...
range
Split
Split Cost
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    Kosh 

    First, create a calculated column in the Sales table to calculate the Year-To-Date (YTD) revenue for each sales person.
    YTD Revenue =
    CALCULATE(
    SUM(Sales[Invoice Amount]),
    FILTER(
    Sales,
    Sales[Sales Person] = EARLIER(Sales[Sales Person]) &&
    Sales[Invoice Date] <= EARLIER(Sales[Invoice Date])
    )
    )

    Next, create a calculated column to determine the commission rate based on the YTD revenue and the Target table.

       Commission Rate =
       VAR CurrentYTD = Sales[YTD Revenue]
       RETURN
       CALCULATE(
           MAX(Target[Commission %]),
           FILTER(
               Target,
               Target[Sales Person] = Sales[Sales Person] &&
               CurrentYTD >= Target[Min] &&
               CurrentYTD <= Target[Max]
           )
       )

     

    Create a Calculated Column for Commission Amount: Finally, create a calculated column to calculate the commission amount for each row.
    Commission Amount =
    Sales[Invoice Amount] * Sales[Commission Rate]


    Combine the Results: You can now combine these columns to get the total commission for each sales person.
    Total Commission =
    SUMX(
    Sales,
    Sales[Commission Amount]
    )
    By following these steps, you will be able to calculate the commission for each row in the Sales table based on the YTD

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

Kosh 

First, create a calculated column in the Sales table to calculate the Year-To-Date (YTD) revenue for each sales person.
YTD Revenue =
CALCULATE(
SUM(Sales[Invoice Amount]),
FILTER(
Sales,
Sales[Sales Person] = EARLIER(Sales[Sales Person]) &&
Sales[Invoice Date] <= EARLIER(Sales[Invoice Date])
)
)

Next, create a calculated column to determine the commission rate based on the YTD revenue and the Target table.

   Commission Rate =
   VAR CurrentYTD = Sales[YTD Revenue]
   RETURN
   CALCULATE(
       MAX(Target[Commission %]),
       FILTER(
           Target,
           Target[Sales Person] = Sales[Sales Person] &&
           CurrentYTD >= Target[Min] &&
           CurrentYTD <= Target[Max]
       )
   )

 

Create a Calculated Column for Commission Amount: Finally, create a calculated column to calculate the commission amount for each row.
Commission Amount =
Sales[Invoice Amount] * Sales[Commission Rate]


Combine the Results: You can now combine these columns to get the total commission for each sales person.
Total Commission =
SUMX(
Sales,
Sales[Commission Amount]
)
By following these steps, you will be able to calculate the commission for each row in the Sales table based on the YTD