topic Calcucate Commission from a Range Table in DAX Commands and Tips

Calcucate Commission from a Range Table

Kosh — Wed, 26 Feb 2025 05:04:19 GMT

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

Re: Calcucate Commission from a Range Table

bhanu_gautam — Tue, 25 Feb 2025 10:10:04 GMT

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

Re: Calcucate Commission from a Range Table

sanalytics — Tue, 25 Feb 2025 12:04:28 GMT

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