parent row %

[skinpup]

Hello

Apologies I have read the instructions for this on Microsoft help page a dozen times but I'm just not getting it and I can't find similar question on our site.

<tbody></tbody>

What setting in the drop down for 'show value as...' would give me the result job end/job start - for each row?

On the pivot table values box in field list I have a job start value and two job end values below that, thought I would be able to get the % of job ends and the actual number in two columns next to each other but I keep getting:

• the % of total job ends rather than job ends/job start
• column goes blank

	Count of Job start	Count of Job end	Count of Job end2
Leeds	200	100	how do i get 100/200 as %
Glasgow	94	7	7/94 %
Bath	16	1	1/16 %
etc etc

<tbody>
</tbody>

What am I doing wrong?

Using excel 2010

 

[Michael M]

Make sure you format your "Count of Job End" column as %

Excel 2007
	A	B	C	D
1		Count of Job start	Count of Job end	Count of Job end2
2	Leeds	200	100	50.00%
3	Glasgow	94	7	7.45%
4	Bath	16	1	6.25%
Sheet1

Cell Formulas
Range	Formula
D2	=C2/B2
D3	=C3/B3
D4	=C4/B4

 

[skinpup]

How can i get the sheet to work this out automatically using parent row% in show value drop down option?

 

[norulen]

i am afraid that this cant be done (My view). % of parent row can only be used as a percentage of the row total and not as a % of some other pivot field. You can only use this the manual percentage feature in your situation i think.
Would be interesting to see if someone else can help you out with it

 

[skinpup]

Thank god! Thought I was going mad, from the blurb on the microsoft website Show different calculations in PivotTable value fields - Excel - Office.com it looks like you can? Maybe I'm getting the columns in the wrong order or something.

Thanks for having a look.

Martin

 

[norulen]

if you notice it all these percentages can be shown by using column total or row total as a basis or one of the value in column or row but not as a percentage of value of a column for the same row.

If you really need that option and if someone is not able to come up with some way of doing this. May be you can try posting your base data and i am sure there would be an alternate way of creating a pivot table which will show % like the one you are looking for

 

[FormR]

How can i get the sheet to work this out automatically using parent row% in show value drop down option?

You can insert a calculated feild; I can't remember the exact steps in XL2003 but in 2010 in on the Pivot Table Options ribbon "Feilds, Items & Steps" > "Calculated Feild"

Excel Workbook
	A	B	C	D
3	Row Labels	Sum of Start	Sum of End	Sum of Field3
4	Bath	396	46	12%
5	Glasgow	1452	73	5%
6	Leeds	56	56	100%
7	Grand Total	1904	175	9%
Sheet4

 

[skinpup]

I was just trying to add the calculated field but it won't work? Keeps returning blank...

It works if I use * or - or + but when i try to use / and *100 to get % it goes blank?

 

[norulen]

The method suggested by FormR seems to be working i also tried it. This is the formual which ultimately gives the result
='Sum of End'/'Sum of Start'

 

[skinpup]

getting somewhere now getting div/0 error!