topic Re: Power BI model for Production Process in DAX Commands and Tips

Power BI model for Production Process

osama_ayoub — Sat, 13 Jan 2024 03:15:17 GMT

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

Re: Power BI model for Production Process

lbendlin — Mon, 15 Jan 2024 00:53:37 GMT

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.

Re: Power BI model for Production Process

osama_ayoub — Mon, 15 Jan 2024 09:12:53 GMT

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

Re: Power BI model for Production Process

lbendlin — Mon, 15 Jan 2024 13:57:03 GMT

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-Forum/ba-p/963216

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/1447523

Re: Power BI model for Production Process

osama_ayoub — Tue, 16 Jan 2024 22:28:14 GMT

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=101026446730583191463&rtpof=true&sd=true

Re: Power BI model for Production Process

osama_ayoub — Fri, 26 Jan 2024 22:28:20 GMT

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