I'm a little confused as to How you want this to happen but to insert add a column to a piviot table, you'll need to include this in your code:Code
- Dim PvtTbl As PivotTable
- Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")
- 'for empty cells in the data area of a PivotTable report, show a specified value, using the PivotTable.NullString Property:
- PvtTbl.NullString = "0"
- PvtTbl.DisplayNullString = True
- 'this would be an example of how to set the field and write the formula
- PvtTbl.CalculatedFields.Add Name:="Variance", Formula:="=IF(OR(Sales=0,Budgeted Sales=0),0,(Sales - Budgeted Sales)/Budgeted Sales)"
- With PvtTbl.PivotFields("Variance")
- .Orientation = xlDataField
- .Function = xlSum
- .Position = 3 'the position where you want the field
- .NumberFormat = "0.00%"
- .Caption = "Variance-%" ' the name of the field
- End With
- End Sub
Hopefully this will give you a start and maybe someone that is a little more familiar with piviot tables can help you refine it to your needs.
Hi MY, I'm not using VBA....I wanted to just setup the pivot table manually using the Fields, Items & Sets box. Thanks anyway.
If you aren't using VBA to do this, then I think you may be in the wrong forum. If what you are looking for is to have just a "Third Column" with the percentage result, that would be a question for the "Formulas" forum. the column would just be exactly that, another column. then the percentage formula would be added to the cell for calculation.
I would at least try to post your question there and see anyone can help you with it.
Have a good day! sorry i couldn't be more help!
Hi MY, I thought I was in 'Excel General' forum.....thanks anyway.
You are right!! My apologies! You are in the General Forum. Not sure why this popped up in the VBA forum room.
Have a great day!
Are Fail and Pass separate columns in your source data, or items in the same column?
OK, and are you trying to see Pass/Fail, or the percentage each one is of the total for the 8 items?
It would really help if you could post a workbook.
You need a calculated item defined as:
=Pass /(Fail +Pass )
but you will have to format those cells as percentage separately.
You can add an additional check like:
=IF(fail+pass=0,0,Pass /(Fail +Pass ))
or just modify the pivot table options to hide errors.
Thank you very much, cheers.