I am joining together two tables of varying granularity, call them Order and orderItem tables. When I complete the join i get an order Item granularity and then I want to taka a value from my Orders table that has now been duplicated on each order Item row and apportion it correctly so that when I add the column the value add up to the correct amount. So an example would be an order with various order items associated to it. The shipping cost is at an order level but I ned to apportion this to each item (not too difficult) but then I need to add up the shipping costs across all the orders to get back to the correct value.
In the attached example I want to get to one result record with a result illustrating 30 (10+20). Item Price has to sum to the total of the individual rows (115.26) so I cannot filter anything.
Any help appreciated.