Power BI BOM data model

[sam_nik12345]

Need help to create a model in Power BI based on my BOM and forecast data sample? My objective is to connect the model with invetory and po to optimize procurement.

plant	id	forecast	goods	component	qty	type	group
x02	xpo34567-pq	xpo34567-pq	0091234521	special-223	400.000	xyz	metaA
x02	xpo34567-pq		qqqq480	01234n	0.006	pqr	metaB
x02	xpo34567-pq		qqqq480	23456t	400.000	abc	metaC
x02	xpo34567-pq		qqqq480	123-223efg	43.104	abc	metaD
x02	xpo34567-pq		qqqq480	dummy	400.000	abc	metaD
x02	xpo34567-pq		qqqq480	water-123	0.140	abc	metaE
x02	xpo34567-pq		qqqq480	apple-123	118.000	abc	metaF
x02	xpo34567-pq		qqqq480	atom-123	400.000	abc	metaG
x02	xpo89012-pc	xpo89012-pc	0091234522	special-224	400.000	xyz	metaA
x02	xpo89012-pc		qqqq422	coco-123	0.006	pqr	metaB
x02	xpo89012-pc		qqqq422	cara-123	0.042	pqr	metaH
x02	xpo89012-pc		qqqq422	01234n	0.028	pqr	metaI

Monthwise forecast

forecast	Jan-18	Feb-18	Mar-18	Apr-18	May-18
xpo34567-pq	5	4	2	6	9
xpo89012-pc	6	1	2	3	4

[Thingsclump]

Hi @sam_nik12345 

I belive you can have 3 tables. One table (Table1) with material IDs (unique). Second one is Bill of material (Table 2). Third one will be forecast table (Table 3). You can create relationship with Table 1 and 2. One more relationship with Table 2 and 3. All other calculations on foreast qty can be done in DAX.

Thanks

Thingsclump

www.thingsclump.com 

[sam_nik12345]

Here I have simplified in two tables. Table 1 & 2 are from BOM data and split into two tables.

Under one matrl there could be multiple forecast in table1

table1:

forecast	matrl
fore1	matrl1
fore2	matrl2
fore3	matrl3
fore4	matrl4
fore5	matrl5
fore1	matrl6
fore1	matrl7
fore3	matrl8
fore3	matrl9
fore3	matrl10

I need to find out forecast for a certain matrl and aggregate that to component level to find out the available stock.

Below table 2 contain matrl, comp and qty. I would like to join these two tables with my forecast table

table2

matrl	Component	qty
matrl1	comp1	1
matrl2	comp2	1
matrl3	comp3	1
matrl4	comp4	1
matrl5	comp5	1
matrl6	comp6	1
matrl7	comp7	1
matrl8	comp8	1
matrl9	comp9	1
matrl10	comp10	1
matrl11	comp11	1
matrl12	comp12	1
matrl13	comp13	1

Monthwise forecast

forecast	Jan-18	Feb-18	Mar-18	Apr-18	May-18
fore1	5	4	2	6	9
fore2	6	1	2	3	4

[v-yingjl]

Hi @sam_nik12345 ,

Not certain what is your expected output based on the description.

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

You can merge these two tables based on id/forecast column to group data first.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sam_nik12345]

Hi @MFelix 

Below my tables; you will notice that, same matlr has multiple comp (tbl2) and comp is connected to forecast and inventory. How do I connect these tables to get matrl & comp level count.

tbl1:

forecast	comp	qty
fore1	comp1	5
fore2	comp2	5
fore3	comp3	5
fore4	comp4	5
fore4	comp5	5
fore6	comp6	5
fore7	comp7	5
fore7	comp8	5
fore7	comp9	5
fore8	comp10	5
fore9	comp11	5
fore10	comp12	5

tbl2:

matrl	Component
matl1	comp1
matl1	comp2
matl1	comp3
matl1	comp4
matl1	comp5
matl1	comp6
matl1	comp7
matl1	comp8
matl1	comp9
matl2	comp10
matl2	comp11
matl2	comp12
matl2	comp13
matl2	comp14

forecast:

forecast	Jan-18	Feb-18	Mar-18	Apr-18	May-18
fore1	5	4	2	6	9
fore2	6	1	2	3	4

I also have inventory, PO tables & GR tables. I want to calculate the total inventory, open PO for prourement planning.

[sam_nik12345]

Hi @v-yingjl 

Below my tables; you will notice that, same matlr has multiple comp (tbl2) and comp is connected to forecast and inventory. How do I connect these tables to get matrl & comp level count.

tbl1:

forecast	comp	qty
fore1	comp1	5
fore2	comp2	5
fore3	comp3	5
fore4	comp4	5
fore4	comp5	5
fore6	comp6	5
fore7	comp7	5
fore7	comp8	5
fore7	comp9	5
fore8	comp10	5
fore9	comp11	5
fore10	comp12	5

tbl2:

matrl	Component
matl1	comp1
matl1	comp2
matl1	comp3
matl1	comp4
matl1	comp5
matl1	comp6
matl1	comp7
matl1	comp8
matl1	comp9
matl2	comp10
matl2	comp11
matl2	comp12
matl2	comp13
matl2	comp14

forecast:

forecast	Jan-18	Feb-18	Mar-18	Apr-18	May-18
fore1	5	4	2	6	9
fore2	6	1	2	3	4