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??

 

[tanquoc0309]

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?

Hi ImkeF,

First, I'm sorry about my explanation, maybe I make you and Scott confuse

SALE: shows the Qty of Product that store sold.
Store A sold 2 product AA11, and sold 3 product BB11

BOM: You know the weight of material (group-level) to make a product.

To make one product AA11, we need 0.1kg of M1
To make one product BB11, we need 0.7kg of M1

=>> To sell 2 product AA11 and 3 product BB11, we need 5 unit M1.
But for each product, we need different weight of material so:
We need 0.2kg M1 to sell 2 product AA11 and 2.1kg M1 to sell 3 product BB11. (Store A need 2.1kg M1 to make 2 product AA11 and 3 product BB11)
First measure is:
Based on SALE and BOM, We need to know the weight of each material that needs for a store
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Store[/TD]
[TD]Material[/TD]
[TD]Qty of Sale[/TD]
[TD]Weight[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]M1[/TD]
[TD]5[/TD]
[TD]2.1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]M2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]M3[/TD]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


For each material, we will have many material-child (detail level)
M2 have 2 details are M2_1 and M2_2, so to make product AA11, we use M2_1 or M2_2, it's ok.

and ACTUAL: They reported Qty of material (detail-level) that they used to make products.
So the second measure is:
We calculate sum of material (detail-level) based on material (group-level), look like:
M2 = Sum of M2_1 and M2_2 (8 + 1) (data on the frist post of this topic)

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Material
(group-level)[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]M1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]M2[/TD]
[TD]8 + 1[/TD]
[/TR]
</tbody>[/TABLE]


And the final measure I want:

[TABLE="class: grid, width: 366"]
<tbody>[TR]
[TD]Store[/TD]
[TD]Material (Group-Level)[/TD]
[TD]Qty of Sale[/TD]
[TD]Weight (kg)[/TD]
[TD]Qty (Sum of Qty in Actual Table)[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]M1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2.1[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]M2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]M3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[/TR]
</tbody>[/TABLE]


Finally, I appreciate both of you

 

[ImkeF]

I've enclosed a file where I've calculated the "QtyOfSale" as the sum of material units that are needed for the sales based on the target production allocation in BOM. The TotalWeight is the weight from BOM * sales qty. These are the "should be" figures.

Then there is sum from the actuals table grouped on material-group level (looked up from costs table) as your last column.

So this is actually a comparison between how many units have acutally been used compared to the units that should have been used according to the BOM.

BTW: The figures in my result table don't match yours, but I think mine are correct.

For the actual figures we could also calculate the actual costs, but for the "should-be" figures according to BOM an unambigous key is missing.

I've used Power Query for this task, so please check if this correct according to your business logic.

If you don't want to use Power Query for this task but Power Pivot instead, I'd like to hand over to Scott again

 

[tanquoc0309]

Thanks very much.
Right now, Your way accord to me.
But it seems you remove Sale_2 table from the file, because I checked, this table is source of the second pivot table.

I forgot Power Query when trying to do this model

 

[ImkeF]

Hm, used Scotts file and there is only 1 sales table in it.

Also cannot spot this table in your descriptions in this threat so far - please help

 

[tanquoc0309]

Where this table come from?

 

[dungkho]

Hi, tanquoc0309,

I think below DAX code that can help you to solve your problem :

Weight (kg) : =
SUMX (
CROSSJOIN (
VALUES ( ProCodes[ProCodes] ),
VALUES ( Materials[Material] )
),
CALCULATE ( SUM ( Sale[QTy] ) ) * CALCULATE ( SUM ( BOM[Weight] ) )
)


Actual Qty : =
SUMX (
VALUES ( MaterialCost[MaterialName-Child] ),
CALCULATE (
SUM ( Actual[Qty] ),
Actual[MaterialName-Child] = EARLIER ( MaterialCost[MaterialName-Child] )
)
)

https://onedrive.live.com/edit.aspx...4B99C319E63236A!105&app=Excel&wacqt=undefined


I am also Vietnamese guy and come from HCMC... Currently, in Vietnam, people use Power Pivot & Power Query is not much. There is no source for us to learn in my country. I have a bit curious about you. Where are you live in Vietnam ?.

 

[tanquoc0309]

Hi, tanquoc0309,

I think below DAX code that can help you to solve your problem :

Weight (kg) : =
SUMX (
CROSSJOIN (
VALUES ( ProCodes[ProCodes] ),
VALUES ( Materials[Material] )
),
CALCULATE ( SUM ( Sale[QTy] ) ) * CALCULATE ( SUM ( BOM[Weight] ) )
)


Actual Qty : =
SUMX (
VALUES ( MaterialCost[MaterialName-Child] ),
CALCULATE (
SUM ( Actual[Qty] ),
Actual[MaterialName-Child] = EARLIER ( MaterialCost[MaterialName-Child] )
)
)

https://onedrive.live.com/edit.aspx...4B99C319E63236A!105&app=Excel&wacqt=undefined


I am also Vietnamese guy and come from HCMC... Currently, in Vietnam, people use Power Pivot & Power Query is not much. There is no source for us to learn in my country. I have a bit curious about you. Where are you live in Vietnam ?.

Hi dungkho,

Thank you for your support.
I am working at etown HCMC
I'm a beginner with Power Pivot & Power Query hope to learn more from you if we can have coffee :P
Please check inbox, that is my skype

 

[ImkeF]

How sweet

Replaced my own rubbish with the Pivot on the new results table now. Looks similar to dungkhos, but includes actual costs