Excel PowerPivot - Grand Totals on Calculated Field

Seth Brundle

I've a spreadsheet making use of PowerPivot. In PP there is a custom calculation which is simply =[Field_X] * RELATED(TableB[Field_Y]). This calculation is adding up the amount of individual items we are producing against the items used to manufacture the items.

This is fine and it displays fine in the eventual pivot tables regardless of the dimensions it is placed against. In the following image, lets assume that the measure M1 is summing the sales of something (and is completely correct). M2 is my calculated field...

However, while the pivot table's subtotals is summing up the M1 totals (in column G) as it should (which is a sum), the M2 is summing up in col H what is already a total and effectively doubling up.

The correct total values for M2 would be...

Dimension C is my ingredients and while item "qwerty b" had 5 items produced, it used 4 "AB"s and 3 "CD"s - we still only produced 5 "qwerty b"s though if you follow.

If I change the context of my total from SUM to AVERAGE, the total would then perform correctly but the calculation of my M2 measure would also change screwing everythign up.

So my question: how should I change my underlying powerpivot formula so that my pivot's subtotals and grand totals aren't doubling up?