Forum Discussion

TuesdayApril's avatar
TuesdayApril
Icon for Helper I rankHelper I
3 years ago

Commission Tiers Summing?

Hello all, I have a system of Commission Tiers across 2 different 'roles'. I cannot get to add totals for. Each tier has a different commission percentage, each of these tier totals need to add together. That's the part I'm having trouble with. I have a separate tier table:

RoleSpread TierSpread Tier SortStartAmountEndAmountCommission%EffectiveStartDateEffectiveEndDate
AE$0.00 - $6,00010600001/1/19001/1/3000
AE$6,001 - $11,0002600111000.11/1/19001/1/3000
AE$11,001 - $15,00031100115000.151/1/19001/1/3000
AE$15,001+4150011000000.21/1/19001/1/3000
RECRUITER$0.00 - $5,000505000.021/1/19001/1/3000
RECRUITER$5,001 - $7,500650017500.041/1/19001/1/3000
RECRUITER$7,501 - $11,0007750111000.081/1/19001/1/3000
RECRUITER$11,001+8110011000000.121/1/19001/1/3000

 

The desired result would add the 4 commission columns filtered to RECRUITER in this case into a Total Commission Value:

Here's the DAX I am using to get Accumilated Spread and Commission:

Accumulated Spread = IF([Weekly Spread] > [Start Amount], MIN([Weekly Spread], [End Amount]) -[Start Amount], 0)
Commission = If([Weekly Spread] > [Start Amount], MIN([Weekly Spread], [End Amount]) -[Start Amount], 0)* [Commission%]
I just need Commision to sum from each tier! 
 
Thanks for the help
Need Help

3 Replies

    • TuesdayApril's avatar
      TuesdayApril
      Icon for Helper I rankHelper I
      3 years ago

      lbendlin  Here you go, sorry for delay. Thanks for the help!

      TimeSheetEntry StateTimeSheetEntry TypeTimeSheerEntryHoursCostRateBillingRateBurdenPercentTimesheetSpread
      ApprovedRegular4060111.21560
      ApprovedOvertime233248.28633.75
      • TuesdayApril's avatar
        TuesdayApril
        Icon for Helper I rankHelper I
        3 years ago

        lbendlin  This is the adjustment table. Adjustment amount and Timesheet spread are added together to get Accumilated Spread, then compared on the tier table above. I feel like I left out a sumx somewhere.

        AdjustmentTupePayPeriodEnddateKeyRecruiter EmployeekeyContractor EmploykeyAdjustmentAmountSpreadWeekCredited Fee
        PERM2/11/2023464512345923.078224000