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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
data_buzz
Frequent Visitor

POWER BI GURU HELP NEEDED

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.

 

need help.PNG

1 ACCEPTED SOLUTION
v-diye-msft
Community Support
Community Support

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:

1.png

Material qty = MAX('Works Order Table'[Qty])*MAX(BOM[Required])
Boxes = [Material qty]/MAX('Storage'[Box qty])
Pallets = BOM[Boxes]/MAX('Storage'[Pallet qty])

2.png

Best regards,

Dina Ye

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

9 REPLIES 9
v-diye-msft
Community Support
Community Support

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:

1.png

Material qty = MAX('Works Order Table'[Qty])*MAX(BOM[Required])
Boxes = [Material qty]/MAX('Storage'[Box qty])
Pallets = BOM[Boxes]/MAX('Storage'[Pallet qty])

2.png

Best regards,

Dina Ye

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Hi,

 

Now I have following problem:

 

I would like to sum measures [Boxes] and [Pallets] (see picture red circles) 

 

table with totals.png

and I have used following:

 

 

"Calculated Column"
Pallet Total = SUMX(
VALUES(BOM[Component Nr]),
[pallets])
 
'And it works, giving me totals which I need, so I though, easy days and created another calculated column for [Boxes]'
 
"Calculated Column"
Box Total = SUMX(
VALUES(BOM[Component Nr]),
[Boxes])
 
...and it gives me "Circular Refference Error" -
A circular dependency was detected: Works Order Table[Box Total], Works Order Table[Pallet Total], Works Order Table[Box Total].
 
I can't figure out why. Its the same SumX function but with different measure. I tried different options and it allowing me to have only one calculated column with SumX function either with [Boxes] or [Pallets] but not both at the same time.
 
One thing to note that my table relationships are Many to Many (only option I can choose, others not available)
 
Please help
 
Thanks
 

Hi Darek,

 

Thanks for the links, I have checked both of them, but still can't figure out whats cousing my issue.

 

Thanks

Anonymous
Not applicable

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 ? 

 

Issue with Distinct values.PNG

Anonymous
Not applicable

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.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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