Calcucate Commission from a Range Table

[Kosh]

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 Target table. 

Example - For Person1
18-Dec-2024  - YTD invoice is 500, so commission @ 5%
13-Jan-2025 - YTD invoice is 2000 (Dec - 500+Jan - 1500), first 500 already paid in Dec, remaining 500 @5% and 1000 @ 6%.
24-Feb-2025 - YTD invoice is 4000 (Dec 500 + Jan - 1500 + Feb 2000) first 2000 already paid, remaining 1500 @ 6% and 500 @ 7%
Total = 235

[sanalytics]

@Kosh 
why the last 500 (24 Feb 2025) is 7%.. It should be 5% based on your range table and commision amount should be 25.
if it is not.then how do you calculate this 7% for 500?
Please clarify this.

Regards

sanalytics

[bhanu_gautam · Accepted solution]

@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