Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have 3 tables:
1 Table - Works order table ( Finished goods on order)
2 Table - Bill of material table ( this table tells me how many materials each finished good contains and what is the requirement to produce 1 piece of the finished good)
3 Table - Storage configuration table ( this table tells me what is the box qty and pallet qty for each material stored in warehouse)
I am trying to calculate, how many boxes I would need to pick from warehouse for each works order.
For example:
Customer ordered 500 units of product A (table 1), that means I am going to bill of material table and multiplying each material for product A :
Salt = 0.1 * 500 = 50 grams
Sugar = 0.15 * 500 = 75 grams
Pepper = 0.11*500 = 55 grams
...I would then devide results with box qty to understand how many boxes I need
Salt = 0.1 * 500 = 50 / 5 = 10 boxes
Sugar = 0.15 * 500 = 75 / 10 = 7.5 boxes
Pepper = 0.11*500 = 55 / 5 = 11 boxes
...then I would divide boxes with pallet qty.
Salt = 0.1 * 500 = 50 / 5 = 10 boxes / 100 = 0.1 pallets
Sugar = 0.15 * 500 = 75 / 10 = 7.5 boxes / 60 = 0.125 pallets
Pepper = 0.11*500 = 55 / 5 = 11 boxes / 150 = 0.07 pallets
I hope I explained enough to understand what I am trying to achieve...
What I am struggling with is to establish the way how to take qty from each works order and flush it through the BOM table.
Solved! Go to Solution.
Hi @data_buzz
I’ve created a sample as yours. You can manage the relationship and use the measure to generate results step by step:
Material qty = MAX('Works Order Table'[Qty])*MAX(BOM[Required]) Boxes = [Material qty]/MAX('Storage'[Box qty]) Pallets = BOM[Boxes]/MAX('Storage'[Pallet qty])
Best regards,
Dina Ye
Hi @data_buzz
I’ve created a sample as yours. You can manage the relationship and use the measure to generate results step by step:
Material qty = MAX('Works Order Table'[Qty])*MAX(BOM[Required]) Boxes = [Material qty]/MAX('Storage'[Box qty]) Pallets = BOM[Boxes]/MAX('Storage'[Pallet qty])
Best regards,
Dina Ye
Hi,
Now I have following problem:
I would like to sum measures [Boxes] and [Pallets] (see picture red circles)
and I have used following:
Hi Darek,
Thanks for the links, I have checked both of them, but still can't figure out whats cousing my issue.
Thanks
If I were you, I'd build a model where all the relationships are 1:many and many:1. There are good reasons for that. One of them is that it's easier to reason about such a model and it's easy to see how filtering one table affects other tables. M2M relationships should only be used IF YOU DO KNOW EXACTLY how they work and how they affect your formulas. Each and every model with M2M can be factored into many-to-one and one-to-many relationships. My advice to you is to create such a model and then write DAX. Cross-filtering is VERY DANGEROUS as well.
Best
Darek
Hi,
Following your advise, I am trying to construct my data with 1:Many or Many:1 relationships...
I have following problem:
My table with warehouse storage information, suppose to have unique values only, however Power Bi telling me that there is difference between number of rows and distinct values ( 7 ).
I have checked this data in SQL server database (original source) and there are 0 duplicates, however in Power Bi its showing below message and I assume this is the reason why I can't select Many:1 relationship with this table.
Does anybody know where is the problem ?
Well, the problem is exactly what Power BI tells you: you do have dups in the column you think has only distinct values. You only think you do have distinct values but you really don't. Please use Power Query to find out which rows in the column are duplicates. You could even calculate this in DAX itself... And one piece of advise: DO NOT TRUST YOUR EYES. Always trust MATHEMATICS. You'll be much better off.
Best
Darek
Thanks for advise
Hi Dina,
You are the star! Brilliant! Exactly what I needed.
Thank you so much.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |