Table to combine three tables to calculate production plan

[Anonymous]

Hi,

I have some issues calculating Production fact and planed (BOM) consumption.

In data model there is five tables:

• Prod Order line - infromation abaout production order
• BOM header - version header, wich is used to join two tables othetrwise it would be many to many realtionship
• BOM line - planed consumption infromation
• Item Ledger Entry - fact consumption infromation (Entry type -5)
• Matierial - Matierial list used in BOM and in Item Ledger Entry

Data Model looks like this:

I created two measures to calculate consumption for 1 unit. (In Item Ledger Entry qty comes in basic unit of measure, meanwile in BOM it's uses unit wich is set in BOM header, so BOM qty I have to divide by conversion rate):

Fact qty per unit = -CALCULATE(SUM('Item Ledger Entry'[Quantity]);FILTER('Item Ledger Entry';'Item Ledger Entry'[Entry Type]=5))/SUM('Prod Order line'[Prod QTY])

BOM QTY per unit = SUM('BOM Line'[BOM quantity])/AVERAGE('Prod Order line'[Conversion])

The problems are:

1. When I try to put all together (Matierial No, Fact QTY, BOM QTY) it shows just those  Matierial records that are in Item Ledger Entry. I would like to see all Matierial that are in ITL and in BOM and if there isn't any record's in ILE, it should show BOM Matierial.
2. When I try to put COGS from Matierial table it brings all Matierial list. It should show just those that have records in ILE or BOM line.

I was thinking about creating aditional table: Order No., Matierial No., Cogs, EUR,  Fact QTY, per unit, BOM qty per unit, but I have no clue how to do this.

Can someone help me or maybe you have other suggestions to solve the problems?

There is pbix file:

https://www.dropbox.com/s/xfv1ei76lr92c4q/Community2.pbix?dl=0

[v-yuta-msft · Accepted solution]

@Anonymous ,

Actually the two problems are both about table relationship, in the sample, table 'Matierial' has one-many relationship with table 'Item Ledger Entry' and 'BOM Line' and the direction is single, so you can only filter from 'Matierial' to both 'Item Ledger Entry' and 'BOM Line'. So when you drag three columns from these three table, it's always use [Material No] in 'Matierial' to filter [Item No_] in 'Item Ledger Entry' and 'BOM Line'. 

In addtion, to achieve what you want, you may reverse the relationship direction from both 'Item Ledger Entry' and 'BOM Line' to 'Matierial'. However, it seems like the relationship type and direction can't be changed. So I would recommend you to add two bridge tables as you mentioned between 'Matierial' and 'Item Ledger Entry' , 'BOM Line'.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @v-yuta-msft