Pivot Table query

[jarl1947]

I have created a pivot table which totals incidents across a range of activities. I want to show each of the incidents as a % of the total in a column alongside the actual totals. I found a past tip which seemed to do just that.
The tip I used suggested dragging 2 versions of the field I which to total, right clicking the second and selecting the % of column option from the resulting dialogue box. WhenI tried it the %'s were displayed as separate rows underneath each of the numerical totals.

Can anyone suggest were I went wrong? Thanks.
I'm using Excel 2000.

 

[Andrew Poulsom]

If you want them side by side, put your mouse over the Data button until the cursor turns into a black cross with arrows at the end. Then click and drag to the right.

 

[jarl1947]

Andrew, thanks for the reply. Tried it but can't get my mouse to produce the black cross with arrows. I know the kind of cursor you're describing but it doesn't seem to work for me. This isn't one of those things that will only work in later versions of Excel,is it?

 

[Andrew Poulsom]

Andrew, thanks for the reply. Tried it but can't get my mouse to produce the black cross with arrows. I know the kind of cursor you're describing but it doesn't seem to work for me. This isn't one of those things that will only work in later versions of Excel,is it?

It works for me in Excel 2000. What does your Pivot Table look like?

 

[jarl1947]

Andrew, her is my pivot table (Hopefully! First time I've tried to copy one to the Board).

July Example.xls
	B	C	D	E
5	Count of Category			Count of Incident Type
6	Category	Total		Category
7	Car crime	3		Car crime
8	Drink/Drug Issue	7		Drink/Drug Issue
9	Env/Litter	61		Env/Litter
10	Harassment	3		Harassment
11	Nuisance behaviour	31		Nuisance behaviour
12	Quads/Youth Disorder	23		Quads/Youth Disorder
13	Tenancy Breach	1		Tenancy Breach
14	Vandalism/Void related	15		Vandalism/Void related
15	Warden Abuse	18		Warden Abuse
16	Grand Total	162		Total
Sheet1

 

[Andrew Poulsom]

If you add Category into the Data field again and set it as % of Column you should get this:

Book4
	C	D	E	F
1	Category	Data	Total
2	Car crime	Count of Category	3
3		Count of Category2	1.85%
4	Drink/Drug Issue	Count of Category	7
5		Count of Category2	4.32%
6	Env/Litter	Count of Category	61
7		Count of Category2	37.65%
8	Harassment	Count of Category	3
9		Count of Category2	1.85%
10	Nuisance behaviour	Count of Category	31
11		Count of Category2	19.14%
12	Quads/Youth Disorder	Count of Category	23
13		Count of Category2	14.20%
14	Tenancy Breach	Count of Category	1
15		Count of Category2	0.62%
16	Vandalism/Void related	Count of Category	15
17		Count of Category2	9.26%
18	Warden Abuse	Count of Category	18
19		Count of Category2	11.11%
20	Total Count of Category		162
21	Total Count of Category2		100.00%
Sheet2

If you mouse over the Data button (D1 in this example) the cursor should change as I described, unless the sheet is protected, in which case the cursor will turn into a hand.

 

[jarl1947]

Andrew, Thanks for your patience. I can get to the situation illustrated in your post. However, once there my mouse pointer stubbornly refuses to change to the familiar black arrowed cross. The sheet is not protected.

I don't know if there is anything else you can suggest. I'll understand if not, but I really am frustated this apparently simple solution will not work for me.
Thanks again.

 

[Andrew Poulsom]

Here is a macro to try:

Sub Test()
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Data")
        .Orientation = xlColumnField
        .Position = 1
    End With
End Sub

 

[jarl1947]

Andrew, how embarassing is this! Tried you Macro (thanks) but got an error message: Run Time Error 1004 "Unable to set Orientation Property of PivotField class" I'm obviously missing something.

 

[Andrew Poulsom]

Andrew, how embarassing is this! Tried you Macro (thanks) but got an error message: Run Time Error 1004 "Unable to set Orientation Property of PivotField class" I'm obviously missing something.

Sorry, I don't know what that something can be, except for worksheet protection. Can you do it with a simple pivot table in a new workbook?