Design Assistance

Question

I am not sure how to approach my report.&nbsp; My 2 tables show sales invoice line items in one and rep % earned in another.&nbsp; They are linked by document number.&nbsp; My data looks like this:&nbsp;&nbsp;Sales Invoice Line Table&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;What should happen:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Document#Sales repCustomerProductSales&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1011122&nbsp;Rep1 get 100%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1011133&nbsp;Rep1 get 100%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1021222&nbsp;Rep1 gets 50% and Rep7 gets 50%&nbsp;&nbsp; No record in Sales Invoice Line for rep 7&nbsp;&nbsp;&nbsp;1021244&nbsp;Rep1 gets 50% and Rep7 gets 50%&nbsp;&nbsp; No record in Sales Invoice Line for rep 8&nbsp;&nbsp;&nbsp;10321033&nbsp;Rep2 gets 100% no record in sales record table defaults to 100%&nbsp;&nbsp;&nbsp;&nbsp;10422222&nbsp;Rep2 gets 100% no record in sales record table defaults to 100%&nbsp;&nbsp;&nbsp;&nbsp;10531344&nbsp;Rep3 gets 50% and Rep7 gets 50%&nbsp; No record in Sales Invoice Line for rep 7&nbsp;&nbsp;&nbsp;10531322&nbsp;Rep3 gets 50% and Rep7 gets 50%&nbsp; No record in Sales Invoice Line for rep 8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sales Record Table&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DocumentSalesRepMargin&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1011100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;102150&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;102750&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;105350&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;105750&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;There is only one record for the sale in the invoice sales line for a rep but we need to split the margin to two reps.Going thru the sales invoice table, I miss the splits.&nbsp;&nbsp;What is the best way to approach this?

v-diye-msft · Answer

Hi&nbsp;Anonymous&nbsp;
&nbsp;
Not quite understand your logic.
&nbsp;
How can we get the below red info?

Document#
Sales rep
Customer
Product
Sales
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;

101
1
1
22
&nbsp;
Rep1 get 100%
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;

101
1
1
33
&nbsp;
Rep1 get 100%
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;

102
1
2
22
&nbsp;
Rep1 gets 50% and Rep7 gets 50%&nbsp;&nbsp; No record in Sales Invoice Line for rep 7
&nbsp;
&nbsp;
&nbsp;

102
1
2
44
&nbsp;
Rep1 gets 50% and Rep7 gets 50%&nbsp;&nbsp; No record in Sales Invoice Line for rep 8
&nbsp;
&nbsp;
&nbsp;

103
2
10
33
&nbsp;
Rep2 gets 100% no record in sales record table defaults to 100%
&nbsp;
&nbsp;
&nbsp;
&nbsp;

104
2
22
22
&nbsp;
Rep2 gets 100% no record in sales record table defaults to 100%
&nbsp;
&nbsp;
&nbsp;
&nbsp;

105
3
13
44
&nbsp;
Rep3 gets 50% and Rep7 gets 50%&nbsp; No record in Sales Invoice Line for rep 7
&nbsp;
&nbsp;
&nbsp;

105
3
13
22
&nbsp;
Rep3 gets 50% and Rep7 gets 50%&nbsp; No record in Sales Invoice Line for rep 8
&nbsp;

anonymous · Answer

Sorry, Excel auto calculated the numbers.&nbsp; There is no rep 8, it should say 7.&nbsp; The sales invoice line only contains one entry that is to be split between two reps.&nbsp; Rep 7 should get 50% of document 102 and 105.&nbsp; The sales record table only contains entries for those documents that are to be split.&nbsp; If a document is not in the table, it goes to the rep in the sales line invoice at 100%.

Forum Discussion

Design Assistance

2 Replies

Related content

DAX assistance needed

Help getting Power BI assistance

Pipeline Design

Assistance with PQE and visuals

Assistance for setting up real time dashboard

		Sales Invoice Line Table
					What should happen:
Document#	Sales rep	Customer	Product	Sales
101	1	1	22		Rep1 get 100%
101	1	1	33		Rep1 get 100%
102	1	2	22		Rep1 gets 50% and Rep7 gets 50% No record in Sales Invoice Line for rep 7
102	1	2	44		Rep1 gets 50% and Rep7 gets 50% No record in Sales Invoice Line for rep 8
103	2	10	33		Rep2 gets 100% no record in sales record table defaults to 100%
104	2	22	22		Rep2 gets 100% no record in sales record table defaults to 100%
105	3	13	44		Rep3 gets 50% and Rep7 gets 50% No record in Sales Invoice Line for rep 7
105	3	13	22		Rep3 gets 50% and Rep7 gets 50% No record in Sales Invoice Line for rep 8


		Sales Record Table

Document	SalesRep	Margin
101	1	100
102	1	50
102	7	50
105	3	50
105	7	50