Need guidance on on schema desing for inventory reports

[mahenkj2]

Hi,

I have a Inventory table, it has individual BatchCode for several materials, so same material name is repeated multiple times:

Batch code	MaterialID	MaterialName	Quantity
A20231	123	Metal	10
A20232	123	Metal	20
A20239	525	Wood	400
A20238	525	Wood	500
A20237	525	Wood	300

I have another table Norms in which each material is entered with raw materials and its consumptions.

MaterialID	MaterialName	RawMaterial	StdConsumption	unit
123	Metal	RM1	4	gm/km
123	Metal	RM2	10	gm/km
123	Metal	RM3	5	gm/km
525	Wood	RM2	6	gm/km
525	Wood	RM5	51	gm/km

So as you see, its many to many relationship between above 2 tables.

I have another table MaterialPrices in which each raw material price is given:

RawMaterial	Price	Unit
RM1	200	$/kg
RM2	400	$/kg
RM3	500	$/kg
RM5	600	$/kg

How to model these tables in power BI, so as to get each batch code's total raw material cost, proportion of raw materials in each batch codes or each material names? Purpose is basically doing analysis for stored inventory with respect to constituent raw materials, prportions and total cost etc.

I have tried creating a bridge table with MaterialName and connect Inventory and Norms together with one to many each side, but I struggle to write measures to calculate above said indicators. What might be a simple design of relationship and some example formulaes.

Thank for all help.

[tharunkumarRTK · Accepted solution]

@mahenkj2 

Since you already tried bridge table technique, I would like to suggest you a different approach which creates a virtual relationship, i.e. using Treatas function: 

I have included all the forumals in the PBIX file attached. Please have a look

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[Ashish_Mathur · Accepted solution]

Hi,

Here's my approach

1. In the Query Editor, merge the MaterialPrice table into the Norms table.  This will create a Price column in Table 2
2. Create a Dim table of 2 columns - Material ID and Material Name
3. Create a Many to One relationship (from the Material ID column of Norms table and the Mateiral ID column of the Inventory table to the table created in 2 above)

Hope this helps.

[mahenkj2]

Hi @tharunkumarRTK 

Thanks.

I will try to use this, one item incase you can check is, that material cost for individual material name does not appear to be correct, when batch code is removed from the context, could you suggest suitable changes pls?

[tharunkumarRTK]

@mahenkj2 

Thank you for reviewing the file. In my observation, it is working as per the formula I wrote. For example, for material type 'Metal' there are two batch codes so the material code 123 will appear twice. 

Material code 123 corresponds to three raw materail codes and total cost of three raw materails is 1100 (500+400+200). If we do the math, it would be two times total raw materail cost i.e 2200 and the quantity of these two batch codes is 30. So 30 * 2200 = 66000. 

I gave you the measure as an example syntax and you can change it as per your requirements.

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[tharunkumarRTK · Accepted solution]

@mahenkj2 

Since you already tried bridge table technique, I would like to suggest you a different approach which creates a virtual relationship, i.e. using Treatas function: 

I have included all the forumals in the PBIX file attached. Please have a look

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!