Adding a spread of one row to all of the other rows

[dknispel]

I'm stuck on this problem.  In a report I'm trying to spread the amount from one row across all of the other rows of the report.  The amount added is based on their percentage of the total (less that one row).  In the example below, the total from Daniel's account would get split up and added to the total for each account, and Daniel's account would go to zero.

So the report would look like this

Parent	Child	Desription	Total	% of Total (less Dan's)	Add to	Total to Chargeback
10000	10001	Albert's Account	5819.94	34.32%	1,509.22	7,329.16
10000	10002	Daniel's Account	4397.13	-100.00%	(4,397.13)	-
10000	10003	David's Account	6412.27	37.82%	1,662.82	8,075.09
10000	10004	Donald's Account	4724.24	27.86%	1,225.09	5,949.33
Total			21353.58	0.00%	-	21353.58

I've made an attempt.  It is in https://drive.google.com/open?id=0B3kwDNYgqUTYNWFua1N0dEVINUU

The problem with my attempt is that is relies on the totals at the end of the report and doesn't work when you filter by invoice.  

Anyone have any ideas how I should fix this?

The definitions for the columns in the report.

Column 1 "Parent" comes from the "Parent" column in the Detail Table

Column 2 "Child" comes from the "Child" column in the Detail Table

Column 3 "Description" comes from the "Description" column in the Master data

Column 4 "Total" comes from the "TotalCost" column in the Detail Table

Column 5 "% of Total Less Dan's" is equal to the rows subtotal of "Total" divided by the calculation of the grand "Total" minus the subtotal of "Total" from the row for child account account 10002.  This is the column I don't know how to calculate.

Column 6 "Add to" is equal to Column 5 times the subtotal of Total for child account 10002.

Column 7 "Total to Chargeback" is equal to Column 4 added to Column 6

Detail table

Invoice	Parent	Child	RecordType	CostBeforeTax	Credits	TaxAmount	TaxType	TotalCost
12345	10000	10001	LinkedLineItem	926.12	0	0	None	926.12
12345	10000	10001	LinkedLineItem	1547.52	0	0	None	1547.52
12345	10000	10001	LinkedLineItem	386.88	0	0	None	386.88
12350	10000	10001	LinkedLineItem	19.44	0	0	None	19.44
12350	10000	10001	LinkedLineItem	13.51519	0	0	None	13.51519
12350	10000	10001	LinkedLineItem	926.12	0	0	None	926.12
12351	10000	10001	LinkedLineItem	65.94481	0	0	None	65.94481
12351	10000	10001	LinkedLineItem	1547.52	0	0	None	1547.52
12351	10000	10001	LinkedLineItem	386.88	0	0	None	386.88
12345	10000	10002	LinkedLineItem	771.53	0	0	None	771.53
12345	10000	10002	LinkedLineItem	0	0	0	None	0
12345	10000	10002	LinkedLineItem	1309.72	0	0	None	1309.72
12350	10000	10002	LinkedLineItem	463.06	0	0	None	463.06
12350	10000	10002	LinkedLineItem	12.33	0	0	None	12.33
12350	10000	10002	LinkedLineItem	771.528	0	0	None	771.528
12351	10000	10002	LinkedLineItem	846.66	0	0	None	846.66
12351	10000	10002	LinkedLineItem	19.44	0	0	None	19.44
12351	10000	10002	LinkedLineItem	202.86	0	0	None	202.86
12345	10000	10003	LinkedLineItem	723.36	0	0	None	723.36
12345	10000	10003	LinkedLineItem	173.88	0	0	None	173.88
12345	10000	10003	LinkedLineItem	202.86	0	0	None	202.86
12350	10000	10003	LinkedLineItem	631.57	0	0	None	631.57
12350	10000	10003	LinkedLineItem	926.12	0	0	None	926.12
12350	10000	10003	LinkedLineItem	1547.52	0	0	None	1547.52
12351	10000	10003	LinkedLineItem	173.88	0	0	None	173.88
12351	10000	10003	LinkedLineItem	1309.72	0	0	None	1309.72
12351	10000	10003	LinkedLineItem	723.36	0	0	None	723.36
12345	10000	10004	LinkedLineItem	723.36	0	0	None	723.36
12345	10000	10004	LinkedLineItem	173.88	0	0	None	173.88
12345	10000	10004	LinkedLineItem	202.86	0	0	None	202.86
12350	10000	10004	LinkedLineItem	386.88	0	0	None	386.88
12350	10000	10004	LinkedLineItem	926.12	0	0	None	926.12
12350	10000	10004	LinkedLineItem	1547.52	0	0	None	1547.52
12351	10000	10004	LinkedLineItem	173.88	0	0	None	173.88
12351	10000	10004	LinkedLineItem	202.86	0	0	None	202.86
12351	10000	10004	LinkedLineItem	386.88	0	0	None	386.88
	10000	10001	AcctTotal	5819.94	0	0	None	5819.94
	10000	10002	AcctTotal	4397.128	0	0	None	4397.128
	10000	10003	AcctTotal	6412.27	0	0	None	6412.27
	10000	10004	AcctTotal	4724.24	0	0	None	4724.24
	10000		StmtTotal	21353.58	0	0	None	21353.58

Master table

Account #	Description	Charge Back to
10000	Master Account	Company
10001	Albert's Account	Al's Credit Card
10002	Daniel's Account	Invoice
10003	David's Account	Invoice
10004	Donald's Account	Invoice

[Eric_Zhang]

@dknispel wrote:

In a report I'm trying to add the amount from one row across all of the other rows of the report.  The amount I add is based on their percentage of the total (less that one row).  In the example below, the total from Daniel's account would get split up and added to the total for each account, and Daniel's account would go to zero.

So the report would look like this

Parent	Child	Desription	Total	% of Total (less Dan's)	Add to	Total to Chargeback
10000	10001	Albert's Account	5819.94	34.32%	1,509.22	7,329.16
10000	10002	Daniel's Account	4397.13	-100.00%	(4,397.13)	-
10000	10003	David's Account	6412.27	37.82%	1,662.82	8,075.09
10000	10004	Donald's Account	4724.24	27.86%	1,225.09	5,949.33
Total			21353.58	0.00%	-	21353.58

@dknispel

What is the add to column and Total to Chargeback? Meanwhile I don't find any account description in  the sample data. Please be more specific on the logic from the sample data to the expected output.

[dknispel]

Thanks.  I updated the orginal post with the information you requested.