Pivot SubTotal not calculating the row but the sum instead?! PLEASE HELP ME :(

[Luchi]

Hi,

I'm facing a real problem... and I just cannot seem to find the answer / solution for it (if there is one).
I'm not quite sure how to best explain this, thus I have the below image to help.

In essance, I need to buid a pivot with % (showing the leftover or usage of something).
All in all that's ok, however, when it comes to the "totals" the pivot is taking the SUM % of the sub %, instead of actually calculating the % figures shown in the total again... (sorry but I could not express myself better)

I have treid using a calcualted field... but the results there are even worse... not sure why either. Becuase for an other part (commited utilisation) it worked fine.
One additional note... I did use Maximum Value in the Contract Limit...

Has anyone got a clever idea on how I could get excel to do my bidding, using a trick or a workaround (adding more columns in the data etc...)? Or is this a lost cause? Please please please, I really need help.

Thanks

 

[oldbrewer]

PM me with your Email

 

[RoryA]

if you still have problems PM me with your Email and we can continue offline.

That is against the forum rules - please do not do, or suggest, that.

 

[RoryA]

As regards the problem at hand, a calculated field can't work with Max - it will always use SUM. You need to adjust your source data so that you can produce the limit with a sum, or use Power Pivot if you have it.

 

[Luchi]

It's a shame that you don't allow for attachments... perticularly on an Excel Forum? I think that's the driver in the end, but no harm ment, I'm sure.

 

[Luchi]

Thank you Rory for the reply... so... a hopless case in the end
At least I know where I'm at and where to start next

Thank you!

 

[oldbrewer]

Rory - I have only ever resorted to Email etc when the thread appears dead and nobody else is interested. I doubt that forum members are interested in the tiny detail or reading about me explaining eg what the sumproduct function is.

 

[Luchi]

Hi there,

thank you for the offer, but I belive Rory has just given us the answer... it's not possible within the Pivot.

 

[RoryA]

Rory - I have only ever resorted to Email etc when the thread appears dead and nobody else is interested. I doubt that forum members are interested in the tiny detail or reading about me explaining eg what the sumproduct function is.

Future readers may well require exactly the further detail that you supply offline, which is why the rule exists.

 

[oldbrewer]

OK Rory I will desist - because I never download anything I will have to ignore some posts. Maybe a new sticky on how to copy a spreadsheet for the forum would be helpful.

 

[Luchi]

For the sake of closure... I have now taken Rory's input "a calculated field can't work with Max - it will always use SUM" and as we don't have "PowerPivot" I decided to add a few Queries within my Access db to create a new column summerising the required amounts on a top level with so that once in excel I can use the SUM.
Now of course I'll have more work adjusting my Pivot to look and feel right but that is better than nothing I guess

Thanks for the help and clarifications!