Calculate Parent total based on Child values

[AdrianPeterson]

Hi Experts,

I'm trying to build a flow that will calculate sales price for parent item based component prices e.g. Lamp price (150) made of a bulb (50) and the stand (100).

I've done a fair amount of play to get the data out of entities (data source - D365 Fin & Ops). For this I created arrays, filter actions and summarised the outcome by the following CSV tables:

Table 1 - Parent items

ItemIdParent

PARENT_01

PARENT_02

Table 2 - BOMs for Parent items

ItemIdParent	BOMId
PARENT_01	BOM_01
PARENT_02	BOM_02

Table 3 - BOM Lines for Parent items

BOMId	ItemIdComponent
BOM_01	PARENT_01_COMP_A
BOM_01	PARENT_01_COMP_B
BOM_02	PARENT_02_COMP_A
BOM_02	PARENT_02_COMP_B

Table 4 - Price for BOM Lines for Parent items

ItemIdComponent	Sales Price
PARENT_01_COMP_A	100
PARENT_01_COMP_B	50
PARENT_02_COMP_A	250
PARENT_02_COMP_B	150

Now.. based on the example above, I'd need the power automate to calculate following:

Table 5 - Calculated parent price

ItemIdParent	CalculatedPrice
PARENT_01	150
PARENT_02	400

I'd appreciate any suggestions how to get the price calculated for parents (table 5). I can then later use that data to create records in Dataverse.

[AdrianPeterson]

I tried to use the Union expression (in Compose data operation):

union(variables('Table4'),variables('Table3'),variables('Table2'))

I've then extracted relevant data into CSV table which looks like this:

ItemIdParent	BOMId	ItemIdComponent	SalesPriceComponent
	BOM_01	PARENT_01_COMP_A
	BOM_02	PARENT_02_COMP_A
	BOM_02	PARENT_02_COMP_B
		PARENT_01_COMP_A	350
		PARENT_02_COMP_A	200
		PARENT_02_COMP_B	100
PARENT_01	BOM_01
PARENT_02	BOM_02

I assume if I get the data to the format as in the example below, it will be easier to roll up parent price. I'd appreciate recommendation how to achieve that.

ItemIdParent	BOMId	ItemIdComponent	SalesPriceComponent
PARENT_01	BOM_01	PARENT_01_COMP_A	350
PARENT_02	BOM_02	PARENT_02_COMP_A	200
PARENT_02	BOM_02	PARENT_02_COMP_B	100