Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Difficult Sales Commission Assignment Question

Hi all, I've spent quite a while staring at this, and finally decided "i need help" - much appreciated in your taking the time to understand and assist.

 

To start with my goal, I'm trying to get a method of linking product usage to a sales rep so they can get paid on the number of "txn_units_nbr", but there's some complex logic here that I'm not quite sure how to navigate in power bi. Ideally, I have a measure that is something like "attained txns" which can then be used to slice and dice in visuals with a given employee_id selected (the number would be meaningless without first having a single employee_id selected).

 

Also, as considering ideas, I have complete control over the data model, so if there is a better data model idea for this I'm open to that too.

 

My data model is as follows (not sure what relationships to make for RepMapping, so left disconnected for sample purposes):

KarmaPolice_0-1627489964140.png

 

The business logic for ascribing the txn_units_nbr to a given rep is as follows:

  • The Rep has an "attainment key" (combo of parent account & geo) which must match the "attainment key" of the account
    • Some reps will have only one geo of the parent account, so you'll notice things like AMER being assigned to rep A but EMEA being assigned to rep B
    • All accounts will have multiple reps... so it's not a 1:1 at all... often there will be 4 or more reps who will get "credit" for a given attainment_key
  • The Rep has a "quota group" which ascribes which products they get attainment for, in my sample data there is only one product group represented by the reps ("MFG"), but figure there could be more for sure.
    • These quota groups should filter down the usage transactions to only those which match... matching is mapped from the "products" table to the usage table via an index (product_group is the field in the products table that lines up with quota_group in the repMapping table)
  • Then, last but certainly not least, the rep has a "split %" that they get - and this will vary by attainment_key - which basically says if their split % is "30 %" then if the usage table showed 9 txn_units usage in their attainment_key and their product_group they would get credit for 3 txn_units (30% of 9 rounded up/down to whole txn_units)

 

So i'd like to be able to do maths like:

Employee ID: 123456

Gets attained a total of X txn_units

Y of those txn_units comes from product index 16

Z of those txn_units comes from account A

 

You probably get the gist at this point... Here is a sample pbix with some data in it that could help: Sample PBIX File

 

 

1 REPLY 1
v-yangliu-msft
Community Support
Community Support

Hi  @Anonymous ,

If you want to add the table RepMapping to this mode, you must have the same column that can be matched with a table, so that you can create a relationship.

 

It’s not very clear that you end up with a description... Can you tell you what the final expected result will look like?

This is a related link, I hope to help you:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.