How to Calculate Sales Revenue Split

[Anonymous]

2 sales reps work on the same account, revenue split percentage is for 1st sales rep, 2nd sales rep will take the balance as revenue.  Business unit want to see the performance of sales

• revenue achieved as the 1st sales rep
• revenue achieved as the 2nd sale rep
• total revenue 

Dataset is as below.

1st Sales Rep	Account Code	Total Amount	SO#	1st Sales Rep Percentage	2nd Sales Rep
Emily	504310	100,000.00	S616232	50%	Julia
Julia	557725	12,000.00	S649320	75%	Emily
Mike	534208	8,186.85	S649320	100%

[AllisonKennedy]

@Anonymous  see update below

Do you have a DimSalesRep table and SalesRepID?

If you have a DimSalesRep table (if you don't have let us know and we can help), then this will work to add their contributed revenue as sales rep _one and as sales rep _two: 

Contributed Revenue =

VAR _One = SUMX(SalesR,SalesR[Total Amount]*SalesR[1st Sales Rep Percentage])

VAR _Two = CALCULATE(SUMX(SalesR,SalesR[Total Amount]*(1-SalesR[1st Sales Rep Percentage])),USERELATIONSHIP(SalesR[2nd Sales Rep],DimSalesRep[Sales Rep]))

RETURN _One+_Two

[AllisonKennedy · Accepted solution]

Or if you want to remove that blank row:

Contributed Revenue =
VAR _One = SUMX(SalesR,SalesR[Total Amount]*SalesR[1st Sales Rep Percentage])
VAR _Two = CALCULATE(SUMX(FILTER(SalesR,SalesR[2nd Sales Rep] <> BLANK() ),SalesR[Total Amount]*(1-SalesR[1st Sales Rep Percentage])),USERELATIONSHIP(SalesR[2nd Sales Rep],DimSalesRep[Sales Rep]))
RETURN _One+_Two

[Anonymous]

Perfect！ I create another 2 measures based on _one and _two to show S1 and S2 in the table. 

Thanks!!!!