Calculate Sales Incentive

[gauravnarchal]

Hello - I want to create an incentive table for my sales team as per the below conditions

First condition

Calculate the Incentive % slab they fall in as per the individual monthly targets

Second condition

Calculate the payout amount based on their individual sales agent payout amount

Table 1 - Individual Sales Agent Targets Table

Sale Agent	Monthly Target
ABC	150,000
DEF	155,000

Table 2 - Sales Table

Sale Agent	Sale Amount	Month
ABC	240,000	Oct-21
DEF	300,000	Oct-21
ABC	100,000	Nov-21
DEF	90,000	Nov-21
ABC	180,000	Dec-21
DEF	220,000	Dec-21

Below (Indivudal) Incentive slab is not a table, can we add the conditions in the DAX/Measure for each sales agent?

Sale Agent - ABC Incentive
From	To	Incentive Amount
0%	20%	2,000
20%	30%	2,500
30%	40%	3,000
40%	50%	3,500
50%	And above	4,000

Sale Agent DEF Incentive
From	To	Incentive Amount
0%	20%	500
20%	30%	1,000
30%	40%	1,500
40%	50%	2,000
50%	And above	2,500

Result First Condition

Result Second Condition

[DataZoe · Accepted solution]

@gauravnarchal I was able to do this:

by creating a Sales Agent table, then joining to the Sales by Month, Target, and Incentives table:

Modeling > New Table:

Sales Agent = DISTINCT('Sales by Month'[Sale Agent])

Relationships:

Sales by Month:

Monthly Targets:

Incentives:

The To and From need to be decimals

Measures:

Sales = sum('Sales by Month'[Sale Amount])

Target Sales = sumx('Sales by Month',sum('Monthly Targets'[Monthly Target]))

Target % =

divide([Sales]-[Target Sales],[Target Sales])

Awarded Incentive =

var t = [Target %]

return

sumx('Sales by Month',

CALCULATE(sum(Incentives[Incentive Amount]),t > Incentives[From] && (t <= Incentives[To] || isblank(Incentives[To]))))

I've also attached the PBIX. 

Hope this helps!