Power BI model for Production Process

[osama_ayoub]

Hi,

I have a model for production process, the problem I have is that I have the fact table with transactional sales have columns of [Item,Category,Date,Quantity,Quantity in bottle,Price per KG) and dimension table of calender table and store table and these dimension table has no duplicate values , but there is another dimension table which is product table (this table has 4 column of [Item,Material,Quantity,Price of Material] t=but this dimension table has duplicate items becasue each item contain many materials so every item is duplicated and some materials are are common between some items also,
now I want to create relationship between the product table and sales table to create measure to calculate
1-the total cost of each item 
2-total materials used every month in separate visual (the quantity of product in sales table and the quanity of each material in material table)
Regards

[osama_ayoub]

@ronrsnfld 
Could you help me please in this data model ?
Regards

[lbendlin]

does each item always have the same set of materials?  In that case you can add another fact table for the materials, and use measures fo your calculations.

[osama_ayoub]

Thanks for Help
No, it is not necessary each item have the same subset of materials , there is only some common materials in each product , could you suggest how can I make the relationships between the tables in this model
Regards

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[osama_ayoub]

Thanks for your Help

the model represent 2 Srore, these stores manufacture some products and we want to monitor the performance of these stores, there are 3 tables 
Table 1 Material Represent the quantity of Materials in each product (there are some common materials) 
Table 2 Sales , represent the sales of the product and the cateogry of each product and also the unit price per each KG from the product and  the quantity in each product (actually the product is a soap Bottle and finally the store in whcih this bottle is saled.
Table 3 Material Price Represent the material Price in each Month.

What I want to create relationships between these tables and create measure to calculate

1-the cost Per Bottle(Product) (Quantity of Material From Table Material and Material Price From Material Price Material (Price are changing over Time)
2-Total Bottles Saled each Month
3-Total Material Used over Month 

4-Total Revenue (Number of Bottles*Quantity in Bottle*Unit Price)

5-Profit Per Bottle and Total Profit per each Product (Bottle)

https://docs.google.com/spreadsheets/d/1LaexpdBhouO0xVQk5ZCLoODHmVmII8tt/edit?usp=sharing&ouid=10102...