Forum Discussion
caculating through the BOM
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
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
2 Replies
- Greg_Deckler
Community Champion
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