I am using Power Pivot to show the items dispatched and the cost incurred for a year. What I ideally want is a pivot with the following layout
Customer A Order B Widget C Dispatched 01/01/12 Qty10 (Then cost broken down by month)
I also want a sum on the quantity of Widgets as we may dispatch several quantities of widgets.
I have linked two tables of values in order to achieve this. The first table has the actual items dispatched with quantities etc and the other has the financial cost associated with the dispatch. I have linked the 2 arrays of data by a unique dispatch ID and not used vlookup on the dispatch data as there is usually more than one financial transaction per dispatch (so I wont capture all the costs). I can’t use vlookup on the financial data as I will double (or treble) up the quantities if there are 2 (or 3) financial transactions per dispatch). There is often additional financial data lines relating to a dispatch when the cost has been amended retrospectively
I have attached what I have so far I am almost there but I have a qty per month when I just want the qty dispatched listed first and not every month.