Forum Discussion
Power BI BOM data model
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 |
5 Replies
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/38490You 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.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 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.
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 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
