Priority Based Sales Rep Assignment

[DataForge_88]

Hello everyone,

I’m currently working on determining the most effective way to assign sales to the appropriate sales representative.

The challenge is that each of the three business units uses a different method for assigning sales, and within those units, individual segments may follow their own distinct rules as well.

What would be the best approach to handle this?

Some examples of the current assignment methods include:

- Proprietary SKUs → Sales rep (This should always be rule #1)
- Business Unit → Ship-to → Sales rep
- Business Unit → Territory → Sales rep 
- Any sale not captured
                   - (This will serve as a fallback for any cases not captured by the rules above; however, it should be minimal, as the                                  primary rules are expected to cover the majority of scenarios.)

[Zanqueta]

Hello @DataForge_88 

The best approach is to create a priority‑based rule engine. Each rule has a defined priority and criteria, and sales are assigned according to the first rule that applies. This ensures consistent results, supports multiple business units with different methods, and provides a clear fallback for unmatched cases. The approach is scalable, transparent, and easy to maintain.

You can build a rule table that contains:
A priority number (1 is highest)
A rule type (SKU, Ship‑to, Territory, and so on)
The criteria for the rule
The Sales Representative to assign
For example:
Priority 1: Proprietary SKU → Sales Rep
Priority 2: Business Unit → Ship‑to → Sales Rep
Priority 3: Business Unit → Territory → Sales Rep
Priority 99: Fallback for any sale not captured by previous rules
Using priority numbers ensures that the system always respects the most important conditions first, such as “Proprietary SKU”.
 
Priority Rule Type Condition Sales Rep
1 Proprietary SKU SKU in ProprietaryList Rep A
2 BU → Ship‑to BU = X AND ShipTo = Y Rep B
3 BU → Territory BU = X AND Territory = Z Rep C
99 Fallback Always TRUE Default Rep
 
This method can be implemented in Power BI (Power Query or DAX) or directly in your data warehouse.
The general rule is:
Check Rule 1
If it does not match, check Rule 2
If that also does not match, check Rule 3
If none match, apply the fallback rule
This structure is much easier to maintain than embedding several conditional statements in DAX
 

Assigned Sales Rep :=
VAR Rule1 =
    CALCULATE(
        MAX(RuleTable[SalesRep]),
        RuleTable[Priority] = 1,
        Sales[SKU] IN VALUES(RuleTable[SKU])
    )
 

VAR Rule2 =
    CALCULATE(
        MAX(RuleTable[SalesRep]),
        RuleTable[Priority] = 2,
        Sales[BusinessUnit] = RuleTable[BusinessUnit],
        Sales[ShipTo] = RuleTable[ShipTo]
    )
 

VAR Rule3 =
    CALCULATE(
        MAX(RuleTable[SalesRep]),
        RuleTable[Priority] = 3,
        Sales[BusinessUnit] = RuleTable[BusinessUnit],
        Sales[Territory] = RuleTable[Territory]
    )
 

RETURN
    COALESCE(Rule1, Rule2, Rule3, "Fallback Rep")