Shortages calculation

[databi_nerd]

Hi PBI Community Members,

I need help building measure logic to calculate shortages and below is the logic and scenario.

Core tables
---> Products (Dim)
---> Order (Fact)

---> Stocks (Fact)
---> BOM (Dim)

• First of all, I have one Dimension which is Products containing unique columns (in single column ) for finished and semi-finished products.
• Then I have orders where I have an Order for finished products. Orders have * to 1 relationship with Products.
• I have one more table for Stocks where we have stocks for all Products including finished and semi-finished products. Stocks have * to 1 relationship with Products.
• Then I created one measure to calculate Required Production based on [Orders] - [Stocks]
• I have another table where I have Bill of Material (BOM) information, have one column for Finished Product and second column for it's components (semi finished product) like for Finished Product = A1 ---> Semi Finished Product = B1, B2, B3 in short in order to produce A1 I need those 3 component and for that I need to calculate the Stocks first and then each component needs. Products 1 to * BOM (Inactive relationship) & Products 1 to * BOM (Inactive relationship)
  
  

Please feel free to ask me if mor einformation required.

Thanks in advance!

[Greg_Deckler]

@databi_nerd Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[databi_nerd]

Hi @Greg_Deckler ,

Thanks for your reply, I have provided the link to download both excel and the .pdix fie with some data and explaination in text box and also provided the logic in both excel and .pdix file. 

Link: Sample data download link