Hello, its me once again.
I am trying to figure out the best way to give the following report. I have a database "A" in which holds the budget of my product with 3 columns. First column is the item name, second column would be the additional feature of the item (lets name it color for practical purposes) and the 3rd column would be the number of items to be delivered. Then, there´s database "B" which holds all the items that are being delivered within this year. Database "B" has more columns than "A", however they share the same amount items and the same 4 color variety. So what I am trying to do here is a report that shows the following:
Current Budget from Database A, Current deliveries for DataBase B, and I would like to have measures to substract the difference. This difference would be known as a column as "Pending delivery". I also have database "C" with the current inventory of the items with their "colors" as well. So I am trying to answer the following questions.
What is my current budget on x item, and y zone,
How much items do I need to deliver to reach my budget.
How much inventory would there be available after such deliveries, with all the variants.
And basically setting this kind of matrix.
ZONE - ITEM - VARIANT - BUDGET - DELIVERIES - PENDING - CURRENT INVENTORY - SURPLUS/SHORTAGE
COUNTY 1 - Pencil - Blue - 3000. 1000. 2000. 2,500 + 500
COUNTY 2 - Pencil - Red. - 2,500. 1500. 1,000. - 300. - 700
COUNTY 3 - Pencil - Blue. - 2,000. 1200. 800. 800. 0
Notice that every county would have their own independent budget regarding item and variant.
Any help would be greatly appreciated. Should you require more information or a dummy excel sheet let me know. Thank you for your time.
