caculating through the BOM

[erche3]

Hello Masters

I am stucking this for a week , I wonder if someone could  help this out !  I really appreciate !!

I have 3 tables as below>

BOM table

component	product	qty
1001	apple	1
1002	apple	2
1003	apple	3
1001	orange	1
1003	orange	6
1001	pear	1
1002	pear	8
1003	banana	2
1002	banana	4

then I created a new table on unique product list as bridgetable , in order to build relationship with next my Fact table  and get the satistics.

Bridge table

Product

apple

orange

pear

banana

all my statstics is on product level  on fact table

product	period	volume
Apple	Jan	320
Apple	Feb	650
Orange	Jan	110
Orange	Feb	890
Pear	Jan	300
Pear	Feb	400
Banana	Jan	550
Banana	Feb	300

I want to get all the statistics cacualting from the product  level when filtering a component

for example , If I search 1002 , I want to get result on 1002 as below in column " wanted result on component"

component	product	qty	volume on Product	wanted result on Component
1002	apple	2	870	1740
1002	pear	8	700	5600
1002	banana	4	850	3400

I tried measure > Sum( facttable{volume}) * sum(BOM{qty} ,  if you could get a correct volume on component level caculated with qty, but the volume from subtotal is wrong.

if I use sumx , it seems there is not relationship on the qty ? 

Besides Merging tables from the query editor ( the real data is quite large with a lot of items and volume records , dont want to mutiple the sizes even more ) , is there any other way to do it and cacuate the volume through the qty in BOM ??

thanks a lot !!!

Eric

[Greg_Deckler · Accepted solution]

Table =
VAR tmpTable = SUMMARIZE(BOM,BOM[component],BOM[product],"Qty",SUM(BOM[qty]),"Volume",SUM(Sales[volume]))
VAR tmpTable1 = ADDCOLUMNS(tmpTable,"wanted result on Component",[Qty]*[Volume])
RETURN tmpTable1

[erche3]

Thanks Greg!