How to Create Calculated Field in Pivot Table Data Model

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for ways to create a calculated field in the Pivot Table data model, then this article will serve this purpose. So, let’s start with our main article to know the detailed procedures of doing this job.


Download Workbook


What Is a Calculated Field?

A calculated field is an integral property of a PivotTable or Pivot Chart. It is also known as a measure. A calculated field or measure is a created field through the use of the DAX formula. If you want to calculate some extra properties of your dataset after completing PivotTable then you can easily apply your formula by creating a calculated field here. This calculated field is generally of 2 types; Implicit Calculated Field, and Explicit Calculated Field. An implicit calculated field is created using the PivotTable Field and an explicit calculated field is created using the PivotTable Analyze tab.


4 Examples to Create Calculated Field in Pivot Table Data Model

Here, we have the following dataset containing the list of salaries along with the working days of some company employees. In the upcoming examples, we will create a calculated field to determine the bonus designated for the employees.

calculated field in pivot table data model

Here, we have used Microsoft Excel 365 version for creating this article, you can use any other version according to your convenience.


Example-1: Creating Implicit Calculated Field in Pivot Table Data Model

In this example, we will convert the following dataset into PivotTable. Then insert a calculated field calculating the bonus for the employees which will be 30% of their salaries.

calculated field in pivot table data model


Step-01: Creating Pivot Table

Here, we will create a PivotTable to insert a calculated field in the data model.

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

implicit calculated field

After that, 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

Afterward, you will be taken 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

Then, the following PivotTable will appear on the left side.

implicit calculated field


Step-02: Adding Measure to Enter Calculated Field

In this step, we will add a measure or calculated field by using a DAX formula.

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

calculated field in pivot table data model

Consequently, 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. Here, we want the Sum of Salary field and so after typing s, you will see various options. Among the options, we will select the [Sum of Salary].

implicit calculated field

  • Now, write down the complete formula
=[Sum of Salary]*0.03

Here, we have 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

In this way, our calculated field Bonus will appear in the field list.

implicit calculated field

  • Drag down the Bonus field to the Values

After that, 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 we will have the following figure.

implicit calculated field

Read More: How to Create a Data Model in Excel (3 Handy Ways)


Example-2: Creating Implicit Calculated Field with Power Pivot Tab

In this example, we will create the calculated field Bonus by using the DAX formula with the help of the Power Pivot tab.

calculated field in pivot table data model


Step-01: Enabling Power Pivot Option

If you don’t see the Power Pivot tab in your Excel worksheet, follow this step to activate this option.

  • Go to the File

power pivot

  • Now, select Options.

calculated field in pivot table data model

After that, you will have the Excel Options wizard.

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

power pivot

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

power pivot

Afterward, 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

As a result, 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

Then, 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

Then, 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

We can change the formatting here also.

  • Choose Currency as Category and keep the Decimal places up to 2.
  • Finally, press OK.

power pivot

After that, 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 we will have the final look like this figure.

power pivot

Read More: How to Get Data from Data Model in Excel (2 Easy Methods)


Example-3: Generating Explicit Calculated Field in Pivot Table Data Model

Here, we will use the Calculated Field directly to generate an explicit calculated field with a formula to determine the bonus amounts for the employees.

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

Then, 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

Afterward, the Insert Calculated Field dialog box will pop up.

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

calculated field in pivot table data model

  • Click on the Salary field and press the Insert Field

explicit calculated field

After that, 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

Finally, 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 we will have the final look like this figure.

explicit calculated field

Read More: Remove Data Model from Pivot Table in Excel (with Easy Steps)


Example-4: Using Explicit Calculated Field for a Complex Formula in Pivot Table Data Model

Here, we will calculate the bonus values with some conditions, such as, for the employees who have working days more than 250 will get a 50% bonus of their salaries and the rest will receive a 50% bonus of their salaries. To apply this condition, we will use the IF function here.

calculated field 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

Then, 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

Afterward, the Insert Calculated Field dialog box will pop up.

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

complex formula

  • Firstly, 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

In this way, we will insert our required fields into the formula.

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

complex formula

Finally, we 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

Read More: [Fixed!] Excel Data Model Relationships Not Working (6 Solutions)


How to Remove Calculated Field in Pivot Table Data Model

Here, we will show the process of eliminating the calculated field Sum of Bonus 3 from the following Pivot Table.

calculated field in pivot table data model

Steps:

  • Select any cell on the column which is created 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

In this way, we have removed the calculated field.

remove

Read More: How to Remove Table from Data Model in Excel (2 Quick Tricks)


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

If you want to get a list of formulas used in the calculated field, then you can follow this section.

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

Then you will be taken to a new sheet where you will get a detailed list of formulas.

calculated field in pivot table data model


Conclusion

In this article, we tried to create a calculated field in the pivot table data model. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo