Forum Discussion

noircrk's avatar
noircrk
Frequent Visitor
1 year ago
Solved

Calculate Bonus Based on Range

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.

Need Help

8 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    1 year ago

    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

     

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    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-Forum/ba-p/963216

    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/1447523

  • noircrk's avatar
    noircrk
    Frequent Visitor
    1 year ago

    My expected result would be for the Payout column: 

    Row LabelsSoldPayout
    User 111550
    RedIncentive5250
    YellowIncentive6300
    User 29440
    RedIncentive3200
    YellowIncentive6240
    Grand Total20990

     

    Detail Table

    AssignmentNumberCsrNameIncentivePlanSold_Flg
    0HDR76User 1YellowIncentive1
    0HYP39User 2RedIncentive1
    0IK024User 2RedIncentive1
    1YCMJ1User 2RedIncentive1
    32KDGWUser 1YellowIncentive1
    4X80JWUser 1YellowIncentive1
    534FG4User 1YellowIncentive1
    8J6LMNUser 2YellowIncentive1
    8MHP48User 1RedIncentive1
    8SB5UGUser 1YellowIncentive1
    9HG0HHUser 2YellowIncentive1
    A559QHUser 2YellowIncentive1
    CES0R8User 2YellowIncentive1
    CRKYBIUser 1YellowIncentive1
    CW6LWFUser 2YellowIncentive1
    D2ZAKWUser 2YellowIncentive1
    IL0CYHUser 1RedIncentive1
    N7IG2AUser 1RedIncentive1
    O28G65User 1RedIncentive1
    O870PLUser 1RedIncentive1

    Incentive Table

    IDIncentive_NameTierMin_CreditMax_CreditPayout
    100RedIncentiveSeats020
    101RedIncentiveTier 134200
    102RedIncentiveTier 25 50
    111YellowIncentiveSeats020
    112YellowIncentiveTier 135100
    113YellowIncentiveTier 26 40
    • lbendlin's avatar
      lbendlin
      Super User
      1 year ago

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

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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