New Member
May 16, 2019
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
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
Active Member
May 24, 2019
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)