Difficult Sales Commission Assignment Question

[Anonymous]

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

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

[Anonymous]

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