{"id":958,"date":"2022-11-22T20:43:19","date_gmt":"2022-11-22T20:43:19","guid":{"rendered":"https:\/\/www.bizinfograph.com\/resource\/?p=958"},"modified":"2024-03-17T21:48:05","modified_gmt":"2024-03-17T21:48:05","slug":"how-to-add-calculated-field-in-pivot-table","status":"publish","type":"post","link":"https:\/\/www.bizinfograph.com\/resource\/how-to-add-calculated-field-in-pivot-table\/","title":{"rendered":"How to Add Calculated Field in Pivot Table?"},"content":{"rendered":"

How to Add Calculated Field in Pivot Table?<\/h1>\n

Calculated Field in Pivot Table marks the pinnacle of custom data analysis within your dynamic reports, enabling you to delve deeper into your datasets and uncover insights that lie beneath the surface. This powerful feature transforms your approach to data, allowing you to integrate custom calculations directly into your pivot tables, thereby refining your analysis and tailoring your findings to your specific needs. Embrace the sophistication of a Calculated Field in Pivot Table, and let it guide you towards more informed decisions and compelling data narratives, all while streamlining your analytical workflow.<\/p>\n

After creating a pivot table, you frequently find that you need to broaden your research and incorporate additional data and calculations.<\/p>\n

You don\u2019t need to go back and add a new data point to the source data if you can get the additional data point you need by using the current data points in the pivot table. Instead, you can accomplish this using a Calculated Field in Pivot Table.<\/p>\n

What is the calculated item in pivot table?<\/strong><\/h2>\n

There is an opportunity to add more information or computations to the pivot table by using “Pivot Table Calculated Field.” For our reports, we frequently need to include our own unique computations that can make use of different fields in the data collection.<\/p>\n

In the below example, Pivot Table summarizes the sales and profit values for the various items. By using “Pivot Table Calculated Field”, we can know what the profit margin of these items was (where the profit margin is \u2018Profit\u2019 divided by \u2018Sales\u2019)<\/p>\n

\"Calculated<\/p>\n

Add <\/strong>a calculated field <\/strong>in pivot table<\/strong>:<\/strong><\/h2>\n

To insert calculated field, we need a pivot table. Suppose you have a Pivot Table as shown below and you want to calculate the profit margin for each item:<\/p>\n

\"Calculated<\/p>\n

Procedure to create a calculated field in pivot table:<\/strong><\/h3>\n

Step 1: <\/strong>In the Pivot Table, simply pick a cell, outlined in Red below.<\/p>\n

\"Calculated<\/p>\n

Step 2:<\/strong> Go to \u201cPivotTable Tools\u201d in Ribbon >> Analyze >> Fields, Items & Sets >> Calculated Field, outlined in Red below.<\/p>\n

\"Calculated<\/p>\n

\u00a0<\/strong><\/p>\n

Step 3: <\/strong>Specify a name in the “Name” field of the “Insert Calculated Filed\u201d dialog box for the new calculated field.<\/p>\n

Create the formula you desire for the computed field in the Formula field. For determining profit margin, the formula is \u2018= Profit\/ Sales\u2019. The field names can be entered manually or by double-clicking on the field name in the “Fields” box.<\/p>\n

\"Calculated<\/p>\n

Step 4:<\/strong> After clicking on “Add” the dialog box will be closed. The Calculated Field will show up in the list of fields in the \u201cPivotTable Fields\u201d as soon as you add it, outlined in Red below<\/p>\n

\"Calculated<\/p>\n

Step 5:<\/strong> The result looks like below. New column named \u201cSum of Profit Margin\u201d is added, outlined in Red below.<\/p>\n

\"Calculated<\/p>\n

You may now use this calculated field just like any other field in a pivot table (note that you can not use Pivot Table Calculated Field as a report filter or slicer).<\/p>\n

The advantage of utilizing a Calculated Field in Pivot Table is the ability to change the Pivot Table’s format and it will update seamlessly.<\/p>\n

For example, in the case, if I drag and drop Buyer into the rows section, the result will look like<\/p>\n

what is displayed below, with both the buyer and the item’s profit margin reported.<\/p>\n

\"Calculated<\/p>\n

In pivot table calculated field, you can also use advanced formula. Before applying advanced formula, you must know some things, so you will not get any issues. They are:<\/p>\n