Log In
Register
Home
New Member
May 16, 2019
Offline
If the pivot table does not contain the calculation of my interest, how to include my additional variable to the pivot table.
for example- My dataset has number of test failed (5) and number of time that parameter is being tested (10), my interest is to show the test rate (5/10), but this is not part of the pivot table.
July 16, 2010
Offline
Hi Kathy,
Depending on how your data is structured, you can add a calculated field or calculated item to the PivotTable. You can then format that field as a fraction.
Mynda
New Member
May 16, 2019
Offline
Hi Mynda,
i added a calculated field, but this new field only can be listened in the sum value field and it can't be placed in other areas (columns..)
thanks,
Kathy
July 16, 2010
Offline
Correct. It's a calculation, so I'm not sure why you don't want it there. If you don't want it in the values area then you'd have to do this calculation in the source data.
Mynda
Active Member
May 24, 2019
Offline
Hi Kathy, I was reviewing your inquiry, and Mynda proposed an excellent solution, but you mentioned that you would like this new calculation on the columns area of there pivot table... am I correct? Please let us know if your questions has been resolved, else please elaborate more on this subject please. thanks!
Pivot Table Calculated Field
In a pivot table, you can create a new field that performs a calculation on the sum of other pivot fields. For example, in the screen shot below, a calculated field, named Bonus, has been created, and it will calculate 3% of the Total, if the sum of Units is greater than 100.
To add a calculated field:
Select a cell in the pivot table, and on the Excel Ribbon, under the PivotTable Tools tab, click the Options tab (Analyze tab in Excel 2013).
In the Calculations group, click Fields, Items, & Sets, and then click Calculated Field.
Calculate Outside of the Pivot Table
If your pivot table layout won't change, another workaround is to calculate the Subtotals and Totals, outside of the pivot table, in columns to the right. For this technique to work correctly, change the pivot table layout from Compact to Outline Form.
In the screen shot below, the bonus is a calculated field, in column P. Formulas have been added in columns Q, R and S, and column Q has conditional formatting, so it matches the pivot table style.
Formula in cell R12: =IF(L12="",R11,SUM(R11,1))
Formula in cell S12: =IF(M12="",0,P12)
Formula in cell Q12: =IF(L12="Grand Total",SUM(S:S),IF(L12<>"",SUMIF(R:R,R12,S:S),S12))
Copy the formulas down to row 22, where the Grand Total is located.
Then, columns P, R and S could be hidden, leaving only the calculated Bonus in column Q.
1 Guest(s)
