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

Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Excel PowerPivot - Grand Totals on Calculated Field

Seth Brundle · 2023-01-17 09:01:27

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…

17-01-2023 9:01am

#1

Seth Brundle

Moderators, Politics Moderators Posts: 41,235 Mod ✭✭✭✭

Join Date: April 2003

Posts: 40181

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?

0