Commission Calculations - Different rates and targets

[jh292]

Hi team, 

Can anyone help with what is probably an easy fix!

I have two data sources, 

1 - "Order Items" provides (on individual rows) -  Account Manger name (Column A), *the details of the sale* (lots of irrelevant fields), and the contract value of that sale (column Z for example). 
Repeats with other various sales row by row, 

Account Manager	Product	Sales No.	Contract Length Months	One off price	Monthly price	Contract Value
Joe Bloggs	Hats	1	12	500	50	£1,100.00
Suzie Smith	Hats	2	36	700	30	£1,780.00
Suzie Smith	Hats	3	24	650	50	£1,850.00
Suzie Smith	Shirts	4	24	1000	40	£1,960.00
Joe Bloggs	Hats	5	36	800	80	£3,680.00
Suzie Smith	Hats	6	24	450	60	£1,890.00
Suzie Smith	Trousers	7	12	0	40	£480.00
Joe Bloggs	Hats	8	3	100	50	£250.00

2 - "Commission Rates" a sheet with Account Manager names (relationship active against Account manager name between the sheets), their Target, over100% target, 75% target, 50% target, their 100% target commission rate, 75% commission rate, 50% commission rate. 
(Below 50% commission rate is 0%)

Account Manager	Target	100% Target	75% Target	50% Target	100% commission	75% commission	50% commission
Joe Bloggs	£5,000.00	£5,000.00	£3,750.00	£2,500.00	0.3	0.2	0.05
Suzie Smith	£15,000.00	£15,000.00	£11,250.00	£7,500.00	0.2	0.15	0.1

I use a simple measure

Total Contract Value = sum('Order Items'[Contract Value] )

to give me totals per person ("Total Contract Value") in a Matrix Visual. 

Account Manager	Order Contract Value	Commission
Joe Bloggs	£5,030.00	?
Suzie Smith	£7,960.00	?

My issue is calculating the commission.... both based on a certain sales person and where they are against target i.e. above 100%?, between 100-75, 75-50 or below 50%

Can someone help? No problem changing columns etc, can add custom columns if required etc etc.

Many thanks

James

[CNENFRNL · Accepted solution]

Hi, @jh292 

As to the table Commission Rates, it's a bit hard to apply filtering in DAX calculation, so I convert it to the following,

Account ManagerThresholdRate

Joe Bloggs	5000	0.3
Joe Bloggs	3750	0.2
Joe Bloggs	2500	0.05
Suzie Smith	15000	0.2
Suzie Smith	11250	0.15
Suzie Smith	7500	0.1

Accordingly, the measure to calculate commission is authored this way,

Commission = 
VAR __sales = [Total Contract Value]
VAR __comm_rate =
    MAXX (
        CALCULATETABLE (
            FILTER ( 'Commission Rates', 'Commission Rates'[Threshold] <= __sales ),
            TREATAS (
                VALUES ( 'Order Items'[Account Manager] ),
                'Commission Rates'[Account Manager]
            )
        ),
        'Commission Rates'[Rate]
    )
RETURN
    IF ( ISBLANK ( __comm_rate ), 0, __sales * __comm_rate )

I also attach the mockup file for your reference.