Help required to code a '% of total' column
 

Hi folks, I looking to write code to add a % of Total column to my Pivot
table. I'm having trouble because at present I'm getting this:

Jan Feb Total
Product A Sales 100 150 250
% of Total 10% 15% 25%

Product B Sales 150 200 350
% of Total 15% 20% 35%

Product C Sales 200 200 400
% of Total 20% 20% 40%

Total Sales 450 550 1000
Total % of Total 45% 55% 100%

What I want to get is this:

Jan Feb Total % of Total
Product A 100 150 250 25.00%
Product B 150 200 350 35.00%
Product C 200 200 400 40.00%
Total 450 550 1000

My code (or a snippet of it) looks like this:

With pvtQuantities
With .PivotFields("Product")
.Orientation = 1 ' 1=Row
End With

With .PivotFields("Month")
.Orientation = 2 ' 2=Column
End With

With .PivotFields("Sales")
.Orientation = 4 ' 4=Data
End With

'.AddDataField(xlApp.ActiveSheet.PivotTables("Sale s by
Month").PivotFields("Sales"), "% of Total")
With .PivotFields("% of Total")
.Calculation =
Microsoft.Office.Interop.Excel.XlPivotFieldCalcula tion.xlPercentOfTotal
.NumberFormat = "0.00%"
End With

End With

Would appreciate if anyone can suggest where I am going wrong...

Thanks

Help required to code a '% of total' column
 

Note: in the code provided the .AddDataField line being commented out is an
accident, it is part of the original problem as described.

"overcanyon" wrote:

Hi folks, I looking to write code to add a % of Total column to my Pivot
table. I'm having trouble because at present I'm getting this:

Jan Feb Total
Product A Sales 100 150 250
% of Total 10% 15% 25%

Product B Sales 150 200 350
% of Total 15% 20% 35%

Product C Sales 200 200 400
% of Total 20% 20% 40%

Total Sales 450 550 1000
Total % of Total 45% 55% 100%

What I want to get is this:

Jan Feb Total % of Total
Product A 100 150 250 25.00%
Product B 150 200 350 35.00%
Product C 200 200 400 40.00%
Total 450 550 1000

My code (or a snippet of it) looks like this:

With pvtQuantities
With .PivotFields("Product")
.Orientation = 1 ' 1=Row
End With

With .PivotFields("Month")
.Orientation = 2 ' 2=Column
End With

With .PivotFields("Sales")
.Orientation = 4 ' 4=Data
End With

'.AddDataField(xlApp.ActiveSheet.PivotTables("Sale s by
Month").PivotFields("Sales"), "% of Total")
With .PivotFields("% of Total")
.Calculation =
Microsoft.Office.Interop.Excel.XlPivotFieldCalcula tion.xlPercentOfTotal
.NumberFormat = "0.00%"
End With

End With

Would appreciate if anyone can suggest where I am going wrong...

Thanks