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

[Poojara_D12]

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.

[v-karpurapud]

Hi @DataForge_88 

We have not received a response from you regarding the query and were following up to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank You.

[v-karpurapud]

Hi @DataForge_88 

Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to @Zanqueta  for those inputs on this thread.

Could you let us know if the suggested solution resolved your issue? If not, please share any additional details so we can assist further.

Best regards,
Community Support Team.

[Zanqueta · Accepted solution]

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