Commission Measure

[adj87]

Hi Everyone,

I need help building a commission measure. I've found a lot of references which filter the Commission Tier and then multiplies the current sales * max commission percentage, but our company works differently (don't they all).

I have a table of Weekly Sales by Employee (AccountManager), table detailing each employee's Commission Tier (not referenced below EE_ComTier), and a final table of the specifics of each Commission Tier (Name, Low, High, Rate)

Take an Account Manager earning 12,000 in sales for a given week and an example of our commission structure below

Using the code below results in 12,000 * 0.1 = 1,200 which is incorrect.

This is the correct calculation:

3000 * 0.05 +

(6000-3000.01)* 0.075 +

(10000-6000.1) * 0.1 +

2000 * 0.125

Total Coms = 1,025

The weekly sales amount gets deducted through each commission bucket. I tried to adapt the below measures which has gotten me partially to the result I need (max rate * weekly sales).

RateTest =

VAR Sales = [AM Sales]
Return
CALCULATE(MAX(dimComTiers[Rate]),
          FILTER(dimComTiers,
             dimComTiers[SalesLw]<=Sales
             &&dimComTiers[SalesHg]>=Sales
             &&dimComTiers[Tier]=VALUES(AccountManager[Tier])))


ComTest =
VAR Sales = [AM Sales]
Return
SUMX(AccountManagers,SalesTable*[RateTest])

If anyone has any thoughts, I'd be very appreciative thank you!

[some_bih · Accepted solution]

Hi @adj87 I think one of the way is to get Commision Key (column which is not in your overview, but should be) into Sales / Fact table as calculated column on wanted level (order or what you need). Additioanal ideas could be found on link https://www.daxpatterns.com/dynamic-segmentation/ 

Hope this help. Kudos appreciated.

[adj87]

Thanks @some_bih  for the response. I forgot about the use of Patterns in this instance, they will certainly help with a secondary issue I was running into.

I was able to add the commission structures to my sales tables. I included a few IF statements, a SUMMARIZED table to sum the commissions by ID and Week and everything is correct.

The main reason I had dismissed this method is it hard coded the tiers and the purpose of this project is to compare Current Tiers vs Proposed Tiers, so I will just have to find a different way to do that 🙂