Dynamic measure for production

[Anonymous]

Hello,

I have some issue with writing a measure to calculate Planned production material qty for basic unit of measure.
I have tables:

• Matierial - list of the Items
• Unit of measure - the table with unit of measure converersion rates
• Prod_line - production Order line list (one order can have multiple lines) with
• ILE - there is consumption fact for Orders line
• BOM Header - tamplate header for production
• BOM Line - planed consumption qty

Task is to calculate difference between planned BOM Line[BOM qty] and fact consumption ILE [Qty, kg].

The tricky thing is that in ILE table all Qty is calculates in Basic unit of measure and Planed quantity in BOM line may be in every other measure and one BOM can be used in for multiple Items production ( 3-4 Items can use the same BOM).

For this purpose I have to create a measure wich selects right conversion rate and divides from planned BOM qty.

What I did so far:

1.  Find conversion rate for specific Production order. Conversion = LOOKUPVALUE(Unit of measure[Qty per Unit of Measure], Unit of measure[KEY2], Prod line[KEY2])

2. Then divide BOM Line[BOM qty] and Prod line[Conversion]

PLAN Qty, kg = SUM('BOM line'[BOM qty])/MAX('Prod line'[Conversion])

The problems:

1. It brings conversion rates just for fact consumptions

2. In total sums it brings wrong number (calculate not sum, but from average Conversion)

3. In the next step I have to calculate COGS: planed and fact.  It's just planed and Fact quantity multiple with last COGS, EUR/kg. so I need precise calculation in first step.

Can someone help me to write correct measure?

PBIX file:

https://www.dropbox.com/s/7xh42dj5c34jipu/Production.pbix?dl=0

[v-juanli-msft]

Hi @Anonymous

Actually, i don't understand below

"

The tricky thing is that in ILE table all Qty is calculates in Basic unit of measure and Planed quantity in BOM line may be in every other measure and one BOM can be used in for multiple Items production ( 3-4 Items can use the same BOM).

For this purpose I have to create a measure wich selects right conversion rate and divides from planned BOM qty."

in the screenshot, 

[BOM qty] is planed quantity, [Qty, kg] is Fact quantity,  

"conversion" should not be all the same values for all rows,

in the last row, "conversion" should not be blank, it should be a number value,

finally, you want [Qty, kg]/([BOM qty]/"conversion"),

right?

Best Regards

Maggie

[Anonymous]

Hi @v-juanli-msft

It took time to understand NAV production structure, but I will try to explain by the example.

Let's take order GU0000283.

ITEM

This is a production Order of LMPM-0077 Item. This item's basic unit of measure is KG (because [Qty_per Unit of Measure] it has "1"). But also it may have other alternative unit of measure, such as KATILS (1 KATILS is 280 KG) or BACZKA (1 BACZKA is 120 KG).

BOM

This ITEM has assigned BOM template - LMPM-0077 (in this case its eaqual to Item no, but it can be different). BOM's are created seperatelly from items, because one BOM can be used to produce two or more different items. This tamplate shows what matierial in what amount is needed to manufacture the ITEM, it's like receipt. And it can be created in any alternative unit of measure, for this case It's KATILS, and it shows how much of matierial we need to manufacture 280 KG (basic unit of measure) of item.

PRODUCTION ORDER

When you create a production order it brings all matierial from BOM and converts it in Basic unit of measure for tis case - KG, and then employee can edit all information (matierial Item, quantity) and then produce. When you finish production all used matierial with qty are recorded in ILE. The matierial quantity recorded in ILE was used to produce the 1000 kg Item.

If I whant to compare plan and fact I need  to compare the same numbers:

1. What it takes (plan) to produce one basic unit of measure  -  convert Production BOM matierial Qty by Conversion rate according in Unit of measure table.
2. What was used to produce one basic unit of measure - Fact Qty/ Produced qty

The second part is the easiest, for the first part I need help.

Answers to your question:

[BOM qty] is planed quantity, [Qty, kg] is Fact quantity, - YES

"conversion" should not be all the same values for all rows - conversion rate should be the same for one BOM, for planed matierials it should be the same

in the last row, "conversion" should not be blank, it should be a number value, - YES

finally, you want [Qty, kg]/([BOM qty]/"conversion") - not exactly Qty, kg should be calcucated for one production Item (2.) and then compared with planed quantity for one basic unit of measure (1.)

I hope it's more clear to you.

I edited data model a bit by adding Product table:

https://www.dropbox.com/s/2fgjp7arkba2ask/Production.pbix?dl=0

right?