Hi, I'm new to power pivot and just finding my feet.
I'm trying to replicate an inventory model that I used to have, which is composed of stock reports and sales reports. For our products, we have an item code, which is 10 digits, but sometimes has a decimal on the end to indicate pack sizes.
I have therefore related the sales and stock reports on 10 digit codes.
What I want to do, which I thought would be straight forward is have a calculated column that shows the sum of the on hand stock for each bulk code, for each period. What would be the best method for this? For each period, there may be multiple lines of each bulk code, as the data contains lot no, lot status etc...
I have attached an example with a traditional excel sum if solution.
Thanks.
Hi Liam,
I would use Power Query for this. In the file attached I loaded your data on Sheet1 into a table called Source Data.
I then created a new query referencing the Source Data query and created the totals. It's called 'Totals'.
Finally, I merged the two queries matching the stock code and date to bring in the totals (see the last column called Loct Onhand). This final query is called 'Final Data' and is loaded into sheet 2, but you can move it to Power Pivot/Data model, which would be more efficient.
Mynda
P.S. I moved this forum post to the Power Pivot course members area as this is where you should post questions to get my attention more quickly.
