Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.
Solved! Go to Solution.
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
|
Hi,
Here's my approach
Hope this helps.
Hi,
Here's my approach
Hope this helps.
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?
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
|
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
|
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
131 | |
110 | |
96 | |
70 | |
67 |