Priority Based Sales Rep Assignment

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")