Weighting factor

I think I have a weighting problem but I'm not sure. I have a bill of materials that is maintained in a bridge table. In my example here I have two part numbers, V74076M0 and V74301M0 both of which are made of material: M92511400. The V74076 uses 0.023849 kg of material and the V74301 uses 0.027242 kg. The RawMaterial_Qty is a calculated measure which equals the Part Number Qty X the BOM_Factor.

The total RawMaterial_Qty is incorrect because the total BOM_Factor is (I believe) the sum of all the occurances of the BOM_Factor for this material. I believe the correct value that I need to sum will be the BOM_Factor X the Part Number Qty X what percentage of the total it represents. Is this a weighting problem and if so what would be the proper aggregation?
Thanks in advance for any assitance or suggestion.
Bruce

It seems to me that all three of the bottom lines do not make any sense, not just the last two.

From the bottom up, a BOM Factor for a single item does not make any sense because it only means something if it is in relation to another item. It is not a value that can or should be aggregated. Likewise, the Raw Material quantity can only be calculated by summing the raw material quantities used by the parent items, not by applying a meaningless factor to a total number of items produced. And, other than a curiosity, what does it mean that 478,000 items were produced using that material? It is not very meaningful business metric since it is based on multiple produced items of different values and material usage.

This is one of those situations where you should suppress aggregations in the cube. About the only useful aggregate metric is probably the sum of raw material quantity.

In this particular case, the cube is reporting SUM(Part Number Qty) X SUM(BOM_Factor) rather than SUM(Part Number Qty X BOM_Factor). It is not a modeling problem or a weighting problem, but rather an OLAP tool problem.

What tool are you using? There are others in this forum more familiar with particular tools who may be able to help you redefine how aggregates are calculated (and suppress others) if your tool has that facility.

As an afterthought, a workaround may be to precalculate the raw material quantity before loading it into the cube, rather than use a formula in the cube. This resolves the aggregation problem for that metric as the precalculated value is fully additive. I would look for some way to suppress the total weighting factor as it is meaningless.

Thanks so much for the prompt reply. I agree with you completely that BOM_Factor makes no sense when aggregated and Part Number Qty has little value. Actually all I am after is the sum of the raw material quantity. I am using Microsoft SSAS. thanks again,
bruce