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]

where does the 114.51 come from please ?

 

[Luchi]

where does the 114.51 come from please ?

Hi,
that is the SUM I would like to see... basically it's the real % of that row (Commited Limit / Contract Limit).

Hope that helps

 

[Luchi]

So... here is maybe a better way to explain this (see image below).
Basically I'm forced to use the "Max" on the first column results... but I then want to add the % and you can see what happens

Any help anyone?

Thanks

 

[oldbrewer]

it appears to be second column divided by first column so where does the max value come in ?

 

[Luchi]

The best way to imagine this is in the form of "Contracts"... imagin you have a "Guide Contract" with several sub-contracts. Now each sub-contract has a value... however on the highlevel view the important value of all these sub-contracts is the highest (in the example above that would be the 32'956'200).

Thus, when I'm crating the Pivot, I want to see the "Max Values" in that column for the totals... however the % calculation is not using that value but rather the sum of all the sub-contracts.

 

[oldbrewer]

[TABLE="width: 690"]
<colgroup><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]contractnum[/TD]
[TD]contracttot[/TD]
[TD]subcontracts[/TD]
[TD]subcontots[/TD]
[TD]subconcode[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]contract1[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]contract1a[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]contract1b[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]contract1c[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]contract1d[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]contract2[/TD]
[TD]35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]contract2a[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]contract2b[/TD]
[TD]11[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]contract2c[/TD]
[TD]14[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Min of subcontots[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]subconcode[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]my pivot table (with the help of a helper column0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]finds the min values of the subcon totals[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Luchi]

Ok... so it shoudl be possible to do it the other way around too?
What or better how did you creat the "helper" column and what are the calculations behind this?

Could you maybe share your pivot with me? Either via a Dropbox or an other forum or even mail? That would really be of help!

 

[oldbrewer]

the helper column (for row 3) is =mid(c3,9,1) since the 1,2 etc is 9th character.

You will need max rather than min in yours, if you still have problems PM me with your Email and we can continue offline.

Try and explain your problem in more simple terms maybe using my data, then maybe Aladin or others will help.

 

[Luchi]

Hi there,

thank you but I must really not get something here... I'm just not sure where, I simply cannot make it work within my pivot.
If I could just send you the excel Pivot of my example that would help I think? How can I best get my file to you or you send me yours?

and thank you again, I'm really thankful your helping me on this!