If we do not hit our goal we will be forced to close the site.
Current status: https://keepboardsalive.com/
Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
Private Group for paid up members of Boards.ie. Join the club.
Excel PowerPivot - Grand Totals on Calculated Field
-
17-01-2023 10:01AM#1
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?
Help Keep Boards Alive. Support us by going ad free today. See here: .
0