cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
data_buzz Frequent Visitor
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

Accepted Solutions
Community Support Team
Community Support Team

Re: POWER BI GURU HELP NEEDED

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
Community Support Team
Community Support Team

Re: POWER BI GURU HELP NEEDED

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

data_buzz Frequent Visitor
Frequent Visitor

Re: POWER BI GURU HELP NEEDED

Hi Dina,

 

You are the star! Brilliant! Exactly what I needed.

 

Thank you so much.

 

 

data_buzz Frequent Visitor
Frequent Visitor

Re: POWER BI GURU HELP NEEDED

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
 
Super User
Super User

Re: POWER BI GURU HELP NEEDED

data_buzz Frequent Visitor
Frequent Visitor

Re: POWER BI GURU HELP NEEDED

Hi Darek,

 

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

 

Thanks

Super User
Super User

Re: POWER BI GURU HELP NEEDED

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

data_buzz Frequent Visitor
Frequent Visitor

Re: POWER BI GURU HELP NEEDED

Thanks for advise

data_buzz Frequent Visitor
Frequent Visitor

Re: POWER BI GURU HELP NEEDED

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

Super User
Super User

Re: POWER BI GURU HELP NEEDED

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 65 members 982 guests
Please welcome our newest community members: