Add a calculated field to a pivot table
-
-
-
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
Display MoreDim 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?
-
Hi rory, the same column, cheers
-
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.
-
Hi rory, see below with the percentage column added, that is what I want to do with a pivot table. I have attached a workbook, cheers.
-
-
You need a calculated item defined as:
=Pass /(Fail +Pass )
but you will have to format those cells as percentage separately.
-
Thanks rory, works great. I assume I also need to insert iferror or something to prevent the below happening when the cells are empty, cheers.
-
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.
-
Using the CalculatedFields.Add Method to create a calculated field. Refer Image 1a which shows a PivotTable report named "PivotTable1" showing Sales using the summary function of Sum. Image 1b shows the Pivot report with a new calculated field added "Variance-%", using the below code. Note that the source range field names are "Sales" and "Budgeted Sales" on which calculations are based.
Code
Display MoreSub PivotTableCalculatedFields1() 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 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 .NumberFormat = "0.00%" .Caption = "Variance-%" End With End Sub Admin
-
-
Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post
All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.
How to use code tags
Note: no apostrophe in the tags, just used for demonstration here.
['code]
your code goes between these tags
['/code]
Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.
Thanks.
-
Step 1: Create the calculated field
In a worksheet in Tableau, select Analysis > Create Calculated Field.
In the Calculation Editor that opens, give the calculated field a name.
Step 2: Enter a formula
In the Calculation Editor, enter a formula.
Eg. SUM([Pass])/SUM([Fail])
Formulas use a combination of functions, fields, and operators.
Step 3: When finished, click OK.
The new calculated field is added to the Data pane. If the new field computes quantitative data, it is added to Measures. If it computes qualitative data, it is added to Dimensions.
Regards,
Jerry.
-
Thanks Jerry
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!