Multilevel bill of material - adjust quantity between level

[atem]

Hello Everyone, 

I am still Alteryx newbie and I am wondering how to adjust qty between levels in BOM. 

I have multilevel BOM, but each raw material's qty shows qty to produce only 1 unit of upper level subassembly. I need to adjust row's qty to consider qty of upper level subassembly (= how many n qty I need to produce n qty of upper level subassembly).

Below I am sharing an example and screen shot. 

Remark: please note a have many BOMs, not only BOM1 as in the example. I tried my best - but as I said I still learn...

Any solution? Thank you in advance! 

BOM ID	RM ID	level (byte)	qty input (byte)	qty output (expected result)		MyComment
BOM1	rm1	1	1	1		as this is level 1 no change
BOM1	sub2	1	5	5		as this is level 1 no change
BOM1	rm3	2	1	5		change: upper level's qty is 5, so we need 5 rm's qty to produce 5 qty of upper level (5*1)
BOM1	rm4	2	1	5		change: upper level's qty is 5, so we need 5 rm's qty to produce 5 qty of upper level (5*1)
BOM1	sub5	2	3	15		change: upper level's qty is 5, so we need 15 rm's qty to produce 5 qty of upper level (5*3)
BOM1	sub6	3	2	30		change: upper level's qty is 15, so we need 30 rm's qty to produce 15 qty of upper level (15*2)
BOM1	rm7	4	2	60		change: upper level's qty is 30, so we need 60 rm's qty to produce 30 qty of upper level (30*2)
BOM1	rm8	3	3	45		change: upper level's qty is 15, so we need 45 rm's qty to produce 15 qty of upper level (15*3)
BOM1	sub8	1	1	1		as this is level 1 no change
BOM1	rm9	2	3	3		"change": upper level's qty is 1, so we need 3 rm's qty to produce 1 qty of upper level (3*1)
BOM1	rm10	1	1			as this is level 1 no change

[aatalai]

@atem 

look at the multi row tool that should help

[atem]

Unfortunatelly, by using multirow fomula, I was only able to refer to "fixed" n of rows "above", while in my case row line of upper level subassembly might vary, could be 1 above raw material, could be 99 above etc - it depends of BOM structure.