Hello together,
I recently started to work more with BI using larger and more complex datasets and now I'm stuck at a problem with a Bill of material.
I have a huge BOM table with montly production volumes, this BOM table also includes a Sub BOM for a part of the inputs into the final product.
One option I was thinking of was to split the main BOM into 2 tables by adding a second query and filter out the main BOM values Sub BOM values vice versa and then try to join both tables.
Ultimately I need a table where the sub BOM quantities have replaced the intermediate product quantity, so I know how much of which input ID quantity ended in which product type in this month, to know the correct input mix.
I was reading through possible approaches but the other BOM problems posted and hierarchy apporaches didn't really helped me.
I'm also unsure if the split in 2 tables is even necessary or if there is a smarter way to do this from the main table already including both datasets.
Appreciate any suggestions and help how to approach or solve this.
Thanks in advance!
Data example:
Main BOM
| Period | Plant | Product Type | Input ID | Volume |
| 01/01/2021 | F | A | X | 10 |
| 01/01/2021 | F | A | Y 1 | 20 |
| 01/01/2021 | F | A | Z 1 | 15 |
| 01/01/2021 | F | B | X | 5 |
| 01/01/2021 | F | B | Y 1 | 5 |
| 01/01/2021 | F | B | Z 1 | 5 |
| 01/01/2021 | F | C | X | 30 |
| 01/01/2021 | F | C | Z 1 | 10 |
| 01/01/2021 | F | C | Z 2 | 5 |
| 01/01/2021 | F | D | X | 40 |
| 01/01/2021 | F | D | Z 1 | 0 |
| 01/01/2021 | F | D | Z 2 | 0 |
| 01/01/2021 | G | A | X | 100 |
| 01/01/2021 | G | A | Y 2 | 250 |
| 01/01/2021 | G | A | Z 1 | 300 |
| 01/02/2021 | F | A | X | 10 |
| 01/02/2021 | F | A | Y 1 | 5 |
| 01/02/2021 | F | A | Z 1 | 15 |
| 01/02/2021 | F | B | X | 5 |
| 01/02/2021 | F | B | Y 1 | 10 |
| 01/02/2021 | F | B | Z 1 | 5 |
| 01/02/2021 | F | C | X | 30 |
| 01/02/2021 | F | C | Z 1 | 10 |
| 01/02/2021 | F | C | Z 2 | 5 |
| 01/02/2021 | F | D | X | 40 |
| 01/02/2021 | F | D | Z 1 | 0 |
| 01/02/2021 | F | D | Z 2 | 0 |
| 01/02/2021 | G | A | X | 150 |
| 01/02/2021 | G | A | Y 2 | 10 |
| 01/02/2021 | G | A | Z 1 | 200 |
Sub BOM
| Period | Plant | Product ID | Input ID | Volume |
| 01/01/2021 | F | Y 1 | Z 1 | 5 |
| 01/01/2021 | F | Y 1 | Z 2 | 5 |
| 01/01/2021 | F | Y 1 | Z 3 | 15 |
| 01/01/2021 | F | Y 3 | Z 1 | 0 |
| 01/01/2021 | F | Y 3 | Z 2 | 20 |
| 01/01/2021 | F | Y 3 | Z 3 | 0 |
| 01/01/2021 | G | Y 2 | Z 1 | 0 |
| 01/01/2021 | G | Y 2 | Z 2 | 250 |
| 01/01/2021 | G | Y 2 | Z 3 | 0 |
| 01/02/2021 | F | Y 1 | Z 1 | 5 |
| 01/02/2021 | F | Y 1 | Z 2 | 5 |
| 01/02/2021 | F | Y 1 | Z 3 | 5 |
| 01/02/2021 | F | Y 3 | Z 1 | 0 |
| 01/02/2021 | F | Y 3 | Z 2 | 20 |
| 01/02/2021 | F | Y 3 | Z 3 | 0 |
| 01/02/2021 | G | Y 2 | Z 1 | 0 |
| 01/02/2021 | G | Y 2 | Z 2 | 10 |
| 01/02/2021 | G | Y 2 | Z 3 | 0 |
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
