I’m looking to create an inventory dashboard for (construction) products purchased and then assigned to a specific project. There are many purchase orders for product that goes into different warehouses. I created a table for all the purchase orders (not huge <20 lines by 12ish columns but could grow in lines) but I’m having trouble with the how I would show how this stock gets depleted as product is assigned to a project (project table with product name and product need with other info about project). 1 single PO might have 10000 of the same item that gets assigned to 1 or more projects. Any advise on how to structure the tables and what graph might you suggest to show product inventory vs assigned product. Ideally I’d be able to have some slicers to show which PO goes to which project stored in which warehouse quantities unassigned ..etc
Hi Alexander,
I guess, at a minimum, you'll have an Orders table and a Stock table. The Orders table will have the project details, but the Stock table won't (something to keep in mind when considering how you might expect to filter the data i.e. you can't filter the stock table based on a Project Slicer).
You can then write measures that take the order quantities from the stock quantities. You might show this in a column chart with the stock column behind the order column, a bit like a thermometer. See this Actual vs Target chart as an example of how it might look.
Hope that gives you some ideas.
Mynda
Thanks Mynda. Great advise. You mention that the slicer wouldnt filter data on the stock table but couldnt i make a relationship between them so it would?
The slicer will only be able to filter the stock table where that table has the same field as shown in the slicer. e.g. if you want the slicer to filter the stock by project, then how would it do that when the stock table doesn't know which project the stock was originally purchased for and doesn't have a project field in the table?
Try it and you'll see what I mean.
Mynda
