Pivot table grand total sum and percentage of grand total, Excel 2010

[dplhollands]

Hi there,

I'm trying to achieve a pivot table that looks like this:

(I have only managed to take the screenshot by copying and pasting the values and then creating % formulae on row 61)

I've had a good look at the "Show values as" options, tried having two "Grand totals" by making a dummy column in the underlying data entitled "Grand total" and still have no joy.

The best I've come up with so far is this:

Which I've achieved by cheekily having two pivot tables and hiding all but the relevant row:

However, this solution is not very good as if the number of rows of the pivot table changes in the future then I'm concerned about overlap.

It feels like there must be a solution in Excel, it's such a simple idea! Yet I have not managed to find one online despite some fruitless searching.

Sorry that I can't make the data available as it's confidential.

Thank you for reading t his/any help.

 

[mrmmickle1]

Your pictures arent coming through....
but it sounds like you need to:

1.Drag the field with values into the Values "box" in the PivotTable field list. (So now you have duplicate values)
2. Right Click additional field.
3. Select Value field settings
4. Go to the "Show Values As" Tab
5. Select % of Grand Total

 

[dplhollands]

Thanks for the reply, sorry about the images, I can't see how to edit the links now to fix em ... my bad.

So if I do what you suggest I get this (forgive the awkwardly placed cursor help image thing):

This is close to what I need, but no cigar.

I need to have the %'s as a single row below the Grand Total line. i.e showing for each employee (column) their total hours as a proportion of the total hours for the whole job (see first pic in the initial post above).

Duncan

 

[mrmmickle1]

Try one of the other options:

% of Row Total, % of Column Total, % of Parent Row Total...

Not sure. I still can't see your images.. so I don't know what your expected result is supposed to look like.

Can you put them on a file sharing site and provide the hyperlink? Or try to enclose them in img tags?

 [IMG] [/IMG]

 

[dplhollands]

Thanks for the help, I don't know why the IMG html code isn't working (I've enclosed in

up above).

The links to the images are:
What I'm aiming for - http://i.imgur.com/mN4Xiat.png or alternatively Imgur

and imgur is now broken for me ... argh.