Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
mahenkj2
Solution Sage
Solution Sage

Need guidance on on schema desing for inventory reports

Hi,

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

 

Batch codeMaterialIDMaterialNameQuantity
A20231123Metal10
A20232123Metal20
A20239525Wood400
A20238525Wood500
A20237525Wood300

 

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

MaterialIDMaterialNameRawMaterialStdConsumptionunit
123MetalRM14gm/km
123MetalRM210gm/km
123MetalRM35gm/km
525WoodRM26gm/km
525WoodRM551gm/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:

RawMaterialPriceUnit
RM1200$/kg
RM2400$/kg
RM3500$/kg
RM5600$/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.

2 ACCEPTED SOLUTIONS
tharunkumarRTK
Super User
Super User

@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: 

Screenshot 2024-08-17 at 1.39.35 PM.png

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!

PBI_SuperUser_Rank@2x.png

 

 

View solution in original post

Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
mahenkj2
Solution Sage
Solution Sage

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?

mahenkj2_0-1723884800901.png

 

@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!

PBI_SuperUser_Rank@2x.png

 

tharunkumarRTK
Super User
Super User

@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: 

Screenshot 2024-08-17 at 1.39.35 PM.png

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!

PBI_SuperUser_Rank@2x.png

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.