Sharing Sales

[adrianlochan]

I have a Staff Table with Names, Rep Codes and Dates along with other info. The Staff in the table may have multiple Rep Codes and in some cases share Rep Codes. The Rep Code identifies what they are entitled to.
There is the Sales Table that does not have Staff Names, however has Rep Codes and dates to identify which staff should be credited for the sale and for what period, Qtrs for now. Which I can handle with a date table.
There is no disbursement between staff for sharing Rep Codes, they get the total credit for the sale for each Rep Code they have or share.
I am trying to figure out how to set up my model so I can calculate how much of the sales total should be allocated to each Staff.
I've tried the idea of a bridge table, grouping, none have been successful.
Sample of my Staff and Sales Tables:

Staff Name	Rep Code
Elise	EGRE
Elise	GTEG
Elise	HCEG
Elise	JHEG
Elise	NNEG
Elise	RMEG
Gianni	GTEG
Gianni	GTES
Gianni	VANC
Jordan	EGRE
Jordan	GTEG
Jordan	HCEG
Jordan	JHEG
Jordan	JHNS
Jordan	JHOP
Nazlin	JHNS
Nazlin	NSUN
Nazlin	RMNS
Rakesh	NSUN
Rakesh	RMEG
Rakesh	RMEH
Rakesh	RMNS

Effective Date	Rep Code	Total AUM
12/29/2023	EGRE	6.76
12/29/2023	JHNS	22.38
12/29/2023	EGRE	54.6
12/29/2023	JHOP	75.4992
12/29/2023	JHOP	619.1979
12/29/2023	EGRE	1028.19
12/29/2023	JHOP	1371.7997
12/29/2023	JHOP	2074.5327
12/29/2023	JHOP	2302.8703
12/29/2023	JHOP	2411.486
12/29/2023	EGRE	2533.03
12/29/2023	JHOP	3238.5591
12/29/2023	EGRE	4809.9813
12/29/2023	JHOP	4848.776
12/29/2023	JHOP	4887.3595
12/29/2023	RMEG	17917335.84
12/29/2023	JHNS	8455808.236
12/29/2023	JHOP	8213107.53
12/29/2023	JHOP	3463551.404

Any help appreciated.
Adrian

[v-priyankata]

Hi @adrianlochan 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-priyankata]

Hi @adrianlochan 
I wanted to check if you had the opportunity to review the information provided @Ashish_Mathur @MFelix . Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-priyankata]

Hi @adrianlochan 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Ashish_Mathur]

Hi,

Based on the 2 tables that you have shared, show the expected result very clearly.  Still better, would be to share the download link of an MS Excel file with yor formulas there.  I will try to convert those formulas into DAX measures.

[MFelix]

Hi @adrianlochan ,

The problem I see with the data is that you have repeated values for rep codes for the different Staff this will not allow to have a unique value for each one so you won't be able to get that value for that specific staff:

How do you know that the specific use of the rep code is for that user?

[adrianlochan]

Hi @MFelix 
That's a good question about the rep code. Each individual will have repcodes assigned to them. The reason some are shared is to identify partnerships between them to share the credit of sales for the purpose of calculating bonuses. For this team it does vary, some may not share rep codes, some do for that purpose.