EXCEL & MSQUERY 1-to-many with calculated field in pivottable

[jwoo6452]

I have a pivottable that uses MS QUERY to relate three tables: COST TABLE, REVENUE TABLE, and a relating table between the two. They relate 1-to-many with EACH Revenue Item (potentially) containing multiple Cost Codes (and by relation cost amounts).

I am hoping that it is possible to create a pivottable that will show the difference between the sum of the cost amounts and their single revenue amount in the ROW area. What is happening now is that revenue is duplicated for each cost code assigned to the item. Here is an example of one revenue item:
=========================================================
# RevAmt CODE CO CostAmt PROFIT
1 $1,000.00 1.1 WCE $100.00 $900.00
1.11 BAL $99.00 $901.00
1.12 BAL $55.00 $945.00
1.14 BAL $66.00 $934.00
------------------------------------------------------------
1 Total $320.00 $3,680.00
=========================================================

"PROFIT" is the calculated field (RevAmt - CostAmt), but I need it only by EACH #, not by EACH CODE. In this example the PROFIT is really $1,000-$320=$680. Because there are FOUR cost codes assigned to the revenue#, 3x$1000 is added into the calculated "PROFIT." (CostAmt & PROFIT are in the data area, the rest is all in the ROW area of the pivottable).... Please Help!!

THANKS!
JWOO