Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Excel PowerPivot - Grand Totals on Calculated Field

Options
  • 17-01-2023 10:01am
    #1
    Seth Brundle
    Moderators, Politics Moderators Posts: 39,540 Mod ✭✭✭✭


    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...

    image.png

    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...

    image.png

    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?



Advertisement