Relationship many to many

[tanquoc0309]

I have problem with relationship

SALE:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]Pro Code[/TD]
[TD]QTy[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]AA11[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]BB11[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]AA11[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]BB11[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]BB11[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

BOMTECHNICAL

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Pro Code[/TD]
[TD]Material Name[/TD]
[TD]Weight[/TD]
[/TR]
[TR]
[TD]AA11[/TD]
[TD]M1[/TD]
[TD]0.1[/TD]
[/TR]
[TR]
[TD]AA11[/TD]
[TD]M2[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]AA11[/TD]
[TD]M3[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]AA11[/TD]
[TD]M4[/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]BB11[/TD]
[TD]M1[/TD]
[TD]0.7[/TD]
[/TR]
[TR]
[TD]BB11[/TD]
[TD]M5[/TD]
[TD]0.8[/TD]
[/TR]
</tbody>[/TABLE]

MATERIALCOST

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Material Name[/TD]
[TD]MaterialName-Child[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]M1[/TD]
[TD]M1_1[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]M1[/TD]
[TD]M1_2[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]M2[/TD]
[TD]M2_1[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]M2[/TD]
[TD]M2_2[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[TD]M3_1[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[TD]M3_3[/TD]
[TD]700
[/TD]
[/TR]
</tbody>[/TABLE]

ACTUAL

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]MaterialName-Child[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]M1_1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]M2_1[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]M2_2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]M3_3[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]M1_1[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]M2_2[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]M2_1[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


How can I have pivot as below:

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]Pro Code[/TD]
[TD]Material Name[/TD]
[TD]Weight_Sale[/TD]
[TD]Material Cost[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]AA11[/TD]
[TD]M1[/TD]
[TD]= Weight in BOM * Qty in Sale[/TD]
[TD]=Weight_Sale * Qty of Actual * COST[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]M2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]M3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]M4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BB11[/TD]
[TD]M1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]M5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Can i have pivot as above??

 

[scottsen]

The short answer is going to be "yes", but to get you there, it would really help if I can see what your diagram view currently looks like.

 

[tanquoc0309]

Sorry for my English.
I list the questions as below:

We have SALE & BOMTECHNICAL:
SALE:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]Pro Code[/TD]
[TD]QTy[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]AA11[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]BB11[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]AA11[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]BB11[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]BB11[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

BOMTECHNICAL

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Pro Code[/TD]
[TD]Material Name[/TD]
[TD]Weight[/TD]
[/TR]
[TR]
[TD]AA11[/TD]
[TD]M1[/TD]
[TD]0.1[/TD]
[/TR]
[TR]
[TD]AA11[/TD]
[TD]M2[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]AA11[/TD]
[TD]M3[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]AA11[/TD]
[TD]M4[/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]BB11[/TD]
[TD]M1[/TD]
[TD]0.7[/TD]
[/TR]
[TR]
[TD]BB11[/TD]
[TD]M5[/TD]
[TD]0.8[/TD]
[/TR]
</tbody>[/TABLE]



1. How can I have pivot as below?

[TABLE="class: outer_border, width: 750"]
<tbody>[TR]
[TD]Store[/TD]
[TD]Pro Code[/TD]
[TD]Material Name[/TD]
[TD]Qty_Sale[/TD]
[TD]Weight[/TD]
[TD]Total Material[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]AA11[/TD]
[TD]M1[/TD]
[TD]This value will show Qty of Pro Code AA11 of Store A
Ex: 2[/TD]
[TD]This value show weight of material in BOMTECHNICAL
Ex: We need 0.1 M1 for AA11[/TD]
[TD]=Qty_Sale[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]M2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]M3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]M4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BB11[/TD]
[TD]M1[/TD]
[TD]This value will show Qty of Pro Code BB11 of Store A
Ex: 3[/TD]
[TD]We nee 0.7 M1 for BB11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]M5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


2. Can we also have pivot table as below:?


[TABLE="class: outer_border, width: 700"]
<tbody>[TR]
[TD]Store[/TD]
[TD]Material Name

Ex: In store A, we sold Pro AA11 and BB11, to make that pros, we need M1, M2, M3, M4 (for AA11) and M1, M2 (for BB11), so the list should be: M1, M2, M3, M4, M5[/TD]
[TD]Total Material[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]M1[/TD]
[TD]=Qty Sale * Weight

For this example: = 2 * 0.1 + 3 * 0.7

2 is qty sale of pro AA11 that has M1

0.1 is weight of Material M1 we need to make pro AA11

3 is qty sale of pro BB11 that has M1

0.7 is weight of Material M1 we need to make pro BB11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]M2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]M3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]M4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]M5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Can i have pivot as above??

Thanks for your support ;(

 

[scottsen]

What is your native language?

Is a "pro code" a collection of parts? Like a DogHouse (procode=DH104) is 4 posts (Material=PO22), 22 sticks (Material=ST04) and 2 glue (GU56) ?

 

[tanquoc0309]

What is your native language?

My language is Vietnamese

Is a "pro code" a collection of parts? Like a DogHouse (procode=DH104) is 4 posts (Material=PO22), 22 sticks (Material=ST04) and 2 glue (GU56) ?

Yes.

 

[scottsen]

Why couldn't you say, French, Italian or Spanish? I know Power BI folks for *those* languages

Something feels "weird" about the model. I can understand that material M1 may be used in different Quanttieis for different Pro Codes... but I would expect the cost and weight for M1 to always be the same?

 

[tanquoc0309]

Why couldn't you say, French, Italian or Spanish? I know Power BI folks for *those* languages

Something feels "weird" about the model. I can understand that material M1 may be used in different Quanttieis for different Pro Codes... but I would expect the cost and weight for M1 to always be the same?

That is my problem
Material M1 can use for different products and for each products we need different weight of M1

I try with store A based on excel formulas (SUMPRODUCT), result as below

 

[scottsen]

https://www.dropbox.com/s/s714gr1yi9pgx1t/Material.xlsx?dl=0

Sorry it took a bit to get back to you, busy Monday I have played with your model a bit, and I'm hoping it gets you onto the right path...

 

[tanquoc0309]

Thank you very much.

In case we remove Pro Code, the result will be wrong? right?
Total Weight of M1 in Store A should be 2.3 (2* 0.1 + 3*07)

anw, thank you for your time

 

[ImkeF]

Agree with Scott - the modell looks a bit strange:

You have a Bill of Materials table that measures in Weight on a group-level (MaterialName), whereas the actual use of the materials are measured in Qty on detail level (Material Child).

So the BOM is just a plan and the ACTUAL is what is real? (So the BOM would not be needed at all to calculate the costs?)

Or do we have to read it that one needs to take the Weight of the BOM * the Qty actual (but wouldn’t this be weird – what sort of a transaction table would this be if you need to multiply it with another qty-like thing (price would be OK, but not qty…)

So if it is like my first attempt: Then how can one allocate the actual use of material to the Products as the ACTUAL table only shows consumption on Store & Material-level, not on Product Level?
Do you have this figure or does this have to be calculated?

Could this be where you BOM should actually come into play: Being the only table showing which Material groups actually go into which product? Do we then need to consider the weights there as well?