I have a report where I'm trying to calculate the sales dollars per square inches of the item sold. For most orders, the sales dollars are easy, ShipQuantity*PricePerM. However, we have one customer where price is not specified per item, but for the order in total as MiscCharge. If they only order one item, it's still easy at ShipQuantity*MiscCharge.
However, if they order multiple items, that won't work as the MiscCharge could be for 3 different items where they order 3000 of one item, 2000 of another, and 1200 of a third. There's nowhere in the data that 6200 units exists as a number. Each item appears as a separate line item.
I think I need to be able to sum the ShipQuantity by order number, but can't figure out a way to do that and still get the rest of the report to work. Help!
