Calculating Component Usage

[Bindi_Baji]

Good morning,
Sorry if this has been covered before. I am attempting to calculate component usage from a BOM  / Bill of Materials and I'm unsure how to set this up in PBI, realtionship wise etc.
I've looked for other posts but they seem to link to an article that no longer exists in most case. 
My data is as below:

Data table for finished products:

Unique_Code	Production_Code	BOM_No
19001	60009678	PBTEST001
19002	29098576	PBTEST002

Data table for BOMs

BOM_No	Component_No	Component_Qty
PBTEST001	28992	0.018
PBTEST001	30066	0.025
PBTEST001	30063	1
PBTEST001	28503	1
PBTEST002	23668	0.0132
PBTEST002	29529	0.2025
PBTEST002	28397	1
PBTEST002	28866	0.002

Fact table for production:

Production_Date	Production_Code	Packs
22/05/2024 00:00	29098576	692
22/05/2024 00:00	60009678	328
24/05/2024 00:00	29098576	428
24/05/2024 00:00	60009678	628
26/05/2024 00:00	29098576	368
26/05/2024 00:00	60009678	464
28/05/2024 00:00	29098576	540
28/05/2024 00:00	60009678	396
29/05/2024 00:00	29098576	468
29/05/2024 00:00	60009678	708
31/05/2024 00:00	29098576	280
31/05/2024 00:00	60009678	268
02/06/2024 00:00	29098576	480
02/06/2024 00:00	60009678	784
04/06/2024 00:00	29098576	532
04/06/2024 00:00	60009678	848
06/06/2024 00:00	29098576	616
06/06/2024 00:00	60009678	188
08/06/2024 00:00	29098576	448
08/06/2024 00:00	60009678	128
09/06/2024 00:00	29098576	288
09/06/2024 00:00	60009678	332
11/06/2024 00:00	29098576	576
11/06/2024 00:00	60009678	180
13/06/2024 00:00	29098576	608
13/06/2024 00:00	60009678	724
14/06/2024 00:00	29098576	412
14/06/2024 00:00	60009678	492
16/06/2024 00:00	29098576	296
16/06/2024 00:00	60009678	708
17/06/2024 00:00	29098576	324
17/06/2024 00:00	60009678	680
19/06/2024 00:00	29098576	612
19/06/2024 00:00	60009678	600
21/06/2024 00:00	29098576	440
21/06/2024 00:00	60009678	444
23/06/2024 00:00	29098576	424
23/06/2024 00:00	60009678	684
25/06/2024 00:00	29098576	664
25/06/2024 00:00	60009678	844
27/06/2024 00:00	29098576	464
27/06/2024 00:00	60009678	624
29/06/2024 00:00	29098576	516
29/06/2024 00:00	60009678	312
01/07/2024 00:00	29098576	492
01/07/2024 00:00	60009678	388
02/07/2024 00:00	29098576	212
02/07/2024 00:00	60009678	780

Any help would be gratefully received!

[Bindi_Baji]

Hi,
Apologies for bump.
Anyone able to offer any assistance please?

[v-huijiey-msft]

Hi @Bindi_Baji ,

Please try:

Create relationships for the three tables you provided:

Use "BOM_No" to connect "BOMs" to "products", and then use "Production_Code" to connect "products" to "production".

After creating the relationship, it should look like this:

Create a calculated column:

Component_Usage = 
SUMX(
    RELATEDTABLE('BOMs'),
    'BOMs'[Component_Qty] * 'production'[Packs]
)

This formula iterates through each component of each production code, multiplying the component quantity in the BOM by the pack quantity in the production table, and then summing the results.

The visual effect of the page should look like this:

If you have any other questions please feel free to contact me.

The pbix file is attached.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Bindi_Baji]

Hi,
Unoftunately, not what I'm looking for.
Apologies, probably my fault for not being more specific.
Would be something like the below output (or similar)