Calculate Bonus Based on Range

[noircrk]

Hey all,

I need to create a measure that returns a payout amount.  I have the below 4 tables, measure and matrix (obviously a very simplified version).  My problem is creating a measure to disply the Payout amount from Table 3 by Table 2.Name and Table 4.Incentive Name.  The new measure should be [Total Sold]>=Table 3.Min && [Total Sold]<=Table 3.Max return Payout unless it's the last tier then Total Sold * Payout.

Any assistance would be greatly appreciated.

There are relationships for:

Table1.Name->Table2.Name

Table1.Type->Table4.IncentiveName

Table3.Type->Table4.IncentiveName

    

Measure

Total Sold: Sum('Table1'[Sold])

Thank you so very much for your time and consideration.

[v-linhuizh-msft]

Thanks for the replies from lbendlin and PhilipTreacy.

Hi  @noircrk , 

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies , it will be helpful for other members of the community who have similar problems as yours to solve it faster. Thank you very much for your kind cooperation!

Best Regards,
Zhu
Community Support Team

[noircrk]

My expected result would be for the Payout column: 

Row Labels	Sold	Payout
User 1	11	550
RedIncentive	5	250
YellowIncentive	6	300
User 2	9	440
RedIncentive	3	200
YellowIncentive	6	240
Grand Total	20	990

Detail Table

AssignmentNumber	CsrName	IncentivePlan	Sold_Flg
0HDR76	User 1	YellowIncentive	1
0HYP39	User 2	RedIncentive	1
0IK024	User 2	RedIncentive	1
1YCMJ1	User 2	RedIncentive	1
32KDGW	User 1	YellowIncentive	1
4X80JW	User 1	YellowIncentive	1
534FG4	User 1	YellowIncentive	1
8J6LMN	User 2	YellowIncentive	1
8MHP48	User 1	RedIncentive	1
8SB5UG	User 1	YellowIncentive	1
9HG0HH	User 2	YellowIncentive	1
A559QH	User 2	YellowIncentive	1
CES0R8	User 2	YellowIncentive	1
CRKYBI	User 1	YellowIncentive	1
CW6LWF	User 2	YellowIncentive	1
D2ZAKW	User 2	YellowIncentive	1
IL0CYH	User 1	RedIncentive	1
N7IG2A	User 1	RedIncentive	1
O28G65	User 1	RedIncentive	1
O870PL	User 1	RedIncentive	1

Incentive Table

ID	Incentive_Name	Tier	Min_Credit	Max_Credit	Payout
100	RedIncentive	Seats	0	2	0
101	RedIncentive	Tier 1	3	4	200
102	RedIncentive	Tier 2	5		50
111	YellowIncentive	Seats	0	2	0
112	YellowIncentive	Tier 1	3	5	100
113	YellowIncentive	Tier 2	6		40

[lbendlin]

Why does YellowIncentive 6 pay out 300? Why not 240 ?

[noircrk]

Appologies, it should be 240.

[lbendlin · Accepted solution]

see attached

[noircrk]

Thank you, @lbendlin !  This has gotten me very close.

[lbendlin]

Your sample data is not covering your scenarios.  Please provide better sample data.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[PhilipTreacy]

Hi @noircrk 

Download my PBIX file with the data and example below

The data you provided doesn't cover all the scenarios needed to show that the DAX measure works.  So I used this data

Try this

Payout = 

VAR _incentive = SELECTEDVALUE(Table4[Incentive Name])

VAR _qty_sold = SELECTEDVALUE(Table1[Sold])

RETURN

SWITCH(

    TRUE(),

    _incentive = "Primary" && _qty_sold <= 5, 100,

    _incentive = "Primary" && _qty_sold >= 6 && _qty_sold <= 9 , 200,

    _incentive = "Primary" && _qty_sold >= 10, _qty_sold * 50,

    _incentive = "Secondary" && _qty_sold <= 3, 100,

    _incentive = "Secondary" && _qty_sold >= 4 && _qty_sold <= 6 , 200,

    _incentive = "Secondary" && _qty_sold >= 7, _qty_sold * 25,

     0
)

NOTE: I adjusted the bands for the payout so that they do not overlap i.e. you can't have 10 as the uppr limit for one band and have 10 as the lower limit for the next band.

Also, do you really want to reward people with a payout if they achieve 0 sales?  I imagine that you want to change that 0 to a 1 in the lowest bands.  This will mean you also need to adjust the first tests to e.g.

_incentive = "Primary" && _qty_sold > 0 && _qty_sold <= 5, 100,

So, you end up with a matrix like this

Regards

Phil