Forum Discussion
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 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
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]RETURNCALCULATE(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
2 Replies
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]RETURNCALCULATE(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 YTDKosh
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
