cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Table to combine three tables to calculate production plan

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:

Screenshot_12.png

 

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.

Screenshot_13.png

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Table to combine three tables to calculate production plan

@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.

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Table to combine three tables to calculate production plan

@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.

View solution in original post

Highlighted
Anonymous
Not applicable

Re: Table to combine three tables to calculate production plan

Thanks @v-yuta-msft 

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors