How to Create Calculated Field in Pivot Table Data Model: 4 Methods

Method 1 – Creating Implicit Calculated Field in Pivot Table Data Model

Step-01: Creating Pivot Table

  • Go to the Insert tab >> PivotTable dropdown >> From Table/Range option

implicit calculated field

The PivotTable from table or range dialog box will appear.

  • Select the dataset as Table/Range, and click on the New Worksheet
  • Check the Add this data to the Data Model option and press OK.

calculated field in pivot table data model

You will go to a new sheet containing two portions; PivotTable1, and PivotTable Fields.

implicit calculated field

  • Drag down Employee to the Rows area and Salary to the Values

The following PivotTable will appear on the left side.

implicit calculated field


Step-02: Adding Measure to Enter Calculated Field

  • Right-click on the name of the PivotTable (here, it is Range)
  • Choose the Add Measure…

calculated field in pivot table data model

The Measure dialog box will pop up.

  • Type Bonus (or whatever you want) in the Measure Name.
  • In the Formula box after the equal (=) sign type your formula. You want the Sum of Salary field and so after typing s, you will see various options. You will select the [Sum of Salary].

implicit calculated field

  • Write down the complete formula
=[Sum of Salary]*0.03

We multiplied 0.03 with [Sum of Salary] because the bonus will be 30% of an individual’s salary.

  • Press OK.

calculated field in pivot table data model

Our calculated field Bonus will appear in the field list.

implicit calculated field

  • Drag down the Bonus field to the Values.

The new column Bonus will appear with the calculated values.

calculated field in pivot table data model

After adding the currency symbols to the salaries and bonuses, you will have the following figure.

implicit calculated field

 


Method 2 – Creating Implicit Calculated Field with Power Pivot Tab

Step-01: Enabling Power Pivot Option

  • Go to the File tab.

power pivot

  • Select Options.

calculated field in pivot table data model

You will have the Excel Options wizard.

  • Go to the Add-ins tab and click on the dropdown symbol beside the Manage.

power pivot

  • Choose the COM Add-ins option and click Go.

power pivot

The COM Add-ins wizard will open up.

  • Check the Microsoft Power Pivot for Excel option and press OK.

calculated field in pivot table data model

You will see the Power Pivot tab in your workbook.

power pivot


Step-02: Adding Measure to Enter Calculated Field

  • Follow Step-01 of Example-1 to open up the following sheet.

calculated field in pivot table data model

  • Drag down Employee to the Rows area and Salary to the Values.

The following PivotTable will appear on the left side.

power pivot

  • After selecting any cell of your PivotTable, go to the Power Pivot tab >> Measures dropdown >> New Measure

power pivot

You will have the Measure dialog box.

  • Set the Measure name as Bonus 1.
  • Write down the following formula in the Formula box.
=[Sum of Salary]*0.03

calculated field in pivot table data model

You can change the formatting here.

  • Choose Currency as Category and keep the Decimal places up to 2.
  • Press OK.

power pivot

The Bonus 1 column with the bonuses of the employees will be automatically added to the PivotTable.

calculated field in pivot table data model

After adding the Currency symbol to the salaries and border to this table will have the final look like this figure.

power pivot


Method 3 – Generating Explicit Calculated Field in Pivot Table Data Model

Use the Calculated Field directly to generate an explicit calculated field with a formula to determine the employees bonus amounts.

calculated field in pivot table data model

Steps:

  • Follow Step-01 of Example-1 to open up the following sheet.

explicit calculated field

  • Drag down Employee to the Rows area and Salary to the Values.

The following PivotTable will appear on the left side.

calculated field in pivot table data model

  • After selecting any cell of your PivotTable, go to the PivotTable Analyze tab >> Calculations group >> Fields, Items & Sets dropdown >> Calculated Field

explicit calculated field

The Insert Calculated Field dialog box will pop up.

  • Set the Name as Bonus 2.
  • In the Formula box, after the equal sign (=) you will insert our desired field.

calculated field in pivot table data model

  • Click on the Salary field and press the Insert Field.

explicit calculated field

Salary will appear in the Formula box.

explicit calculated field

  • Complete the formula like the following
= Salary*0.03
  • Press OK.

calculated field in pivot table data model

A new column with bonus values will be added to the Pivot Table.

explicit calculated field

After adding the Currency symbol to the salaries and border to this table will have the final look like this figure.

explicit calculated field


Method 4 –Using Explicit Calculated Field for a Complex Formula in Pivot Table Data Model

Steps:

  • Follow Step-01 of Example-1 to open up the following sheet.

complex formula

  • Drag down Employee to the Rows area and Working Days, and Salary to the Values.

The following PivotTable will appear on the left side.

complex formula

  • After selecting any cell of your PivotTable, go to the PivotTable Analyze tab >> Calculations group >> Fields, Items & Sets dropdown >> Calculated Field.

calculated field in pivot table data model

The Insert Calculated Field dialog box will pop up.

  • Set the Name as Bonus 3.
  • In the Formula box after the equal sign (=)you will insert our desired field here.

complex formula

  • Write down IF( to start the formula.

calculated field in pivot table data model

  • Click on the Working Days field and press the Insert Field

complex formula

Insert the required fields into the formula.

  • Apply the following formula.
=IF('Working Days' >250,Salary *0.05,Salary *0.03)
  • Press OK.

complex formula

You will have a new column with calculated bonuses.

calculated field in pivot table data model

After adding the Currency symbol to the salaries, bonuses, and border to this table we will have the final look like this figure.

complex formula


How to Remove Calculated Field in Pivot Table Data Model

Steps:

  • Select any cell on the created column by entering the calculated field and then right-click on it.

remove

  • Choose the Remove “Sum of Bonus 3”

calculated field in pivot table data model

You removed the calculated field.

remove


How to Get a List of Formulas for Calculated Field in Pivot Table Data Model

Steps:

  • After selecting any cell of your PivotTable, go to the PivotTable Analyze tab >> Calculations group >> Fields, Items & Sets dropdown >> List Formulas

list

You will be taken to a new sheet for a detailed formula list.

calculated field in pivot table data model


Download Workbook


<< Go Back to Pivot Table Calculations | Pivot Table in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo