Forum Discussion
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")
Hi DataForge_88
I think the way to handle this is to stop trying to manage the logic through complex DAX and instead centralize everything into a single rule-based mapping table that defines how sales should be assigned. Each rule (SKU-based, Ship-to, Territory, etc.) should be stored as rows in this table along with a priority column, where the most important rule—like Proprietary SKU—is given the highest priority. Then, during data preparation (preferably in Power Query), you match each sales record against this table and assign the sales rep based on the first rule that matches according to priority. This approach ensures consistency across all business units and segments, keeps the logic transparent and easy to maintain, and avoids messy nested conditions in DAX. The fallback rule simply captures anything not matched earlier, but if your mapping is well-designed, it should rarely be used.