If you need to sum specific items in a pivot table field, you can create a calculated item. For example, if your pivot table contains an Order Status field, with four types of status:
- Shipped
- Pending
- Backorder
- Canceled
You could create a calculated item, named Sold, that sums the units sold, for orders with a status of Shipped, Pending, or Backorder.
Create a Calculated Item
Follow these steps to create a calculated item:
- In the pivot table, select a cell that contains an Order Status item. For example, select cell A5, that contains the Backorder item.
- On the Ribbon’s Options tab, in the Tools group, click Formulas, and then click Calculated Item.
- Type a name for the Calculated Item, for example, Sold, and then press the Tab key to move to the Formula box.
- In the Fields list, select Order Status, and in the Items list, double-click Shipped, and then type a plus sign (+).
- Double-click Pending, type a plus sign, and then double-click Backorder. The complete formula is =Shipped+Pending+Backorder.
- Click OK, to save the calculated item, and to close the dialog box.
The new calculated item, Sold, is added to the Row area in the pivot table. However, the Grand Totals have increased, because the Sold item includes the values from other items.
Hide the Unnecessary Pivot Items
In the pivot table, you can hide the Shipped, Pending, and Backorder items, because they are included in the Sold calculated item.
With those pivot items hidden, the pivot table will show the correct Grand Totals.
Download the Sample File
To see the pivot table data and the calculated item, you can download the sample file from the calculated item page on my Contextures website.
In the Download section, click on the first item — Create a Calculated Item sample file.
The file is in xlsx format, and is zipped.
_______________
It would be nice if, when you inserted a calculated item Microsoft also asked you if you wanted to hide any PivotFields in the same dialog box, or maybe in a Wizard-type approach with a second screen. That way you would remember to hide any duplicate items in the PivotTable, a critical step.
Hi,
I want to calculate SLA will met if the difference between current date and request date is less than 60 days.
how could it possible please suggest
@Hunamantha Eate, you could add a field to your pivot table’s source data, to calculate if the date difference is less than 60 days.
Then, add that field to the pivot table, and refresh the pivot table when you open the file each day
I have apivot table with filters utilized to narrow down the data I want to show. However when I insert a calculated field in a column it seems to bypasss the filter and bring all records into the pivot table. How do I limit it to the data I want to filter?