Bill of Materials and Monthly Part Cost

[robofski]

Hi All,

I have a Bill of Materials table:

Part#	Qty
PartA	2
PartB	3

I also have a Part Costs table with the part cost each month

Part#	YearMonth	Cost
PartA	201901	1.25
PartA	201902	1.3
PartA	201903	1.5
PartB	201901	2.6
PartB	201902	2.1
PartB	201903	2.7

I'm trying show the effect of a price change which is very different if the price goes up $1 and you use 1 of them in an item vs going up by a $1 and use 100 of them in a item.

Any ideas?

[v-lionel-msft]

Hi @robofski ,

"I'm trying show the effect of a price change which is very different if the price goes up $1 and you use 1 of them in an item vs going up by a $1 and use 100 of them in a item."

I don't particularly understand the effect you want to achieve.

Could you describe in more detail the visual effect you want to achieve? It is best to show it with an example table.

Best Regards,
Lionel Chen

[robofski]

@v-lionel-msftmy BOM table earlier was probably incomplete, really it looks more like:

Part#	Component Part#	Qty
12345	PartA	2
	PartB	3
56789	PartA	100
	PartB	3

So the resulting table would be something like:

YearMonth	Part 12345	Part 56789
201901	2.5	125
201902	2.6	130
201903	3	150
201901	7.8	7.8
201902	6.3	6.3
201903	8.1	8.1

Show that the small price rise in component PartA has a much bigger impact on the cost of Part 56789 than it does on the cost of part 12345 due to the fact Part 56789 uses significantly more of PartA.

Make more sense?

[v-lionel-msft]

Hi @robofski ,

How is the data in my circle calculated? Could you give me a mathematical formula?

Best regards,
Lionel Chen

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

[robofski]

@v-lionel-msftSorry both entries should have been added together to get the total cost for the Parent Part so the table should have been

YearMonth	Part 12345	Part 56789
201901	10.3	132.8
201902	8.9	136.3
201903	11.1	158.1

Basically

Parent Part Cost = SUM(Component Part Qty X Component Part Cost)

[Aron_Moore]

I would guess the Bill of Materials table has a date column.

You could create a 'key' with part and date and join the tables, or use the key to LOOKUPVALUE the cost that way.

[robofski]

@Aron_Moorethe BOM table doesn't have a date as it's static data and doesn't change, I do see where you're going though, and I guess I could add fields to that table to represent the months and then do the calculation by looking up the value for that month.  Ummm....Thanks for the idea!

[Aron_Moore]

If that's the case, join the tables Part # to Part # and you should be able to create a measure Qty x Cost.