1. Calculated Fields
Sometimes you will want to include a field in your pivot table, but it may not exist in the original dataset. If the new field can be calculated from the existing data, then your answer may be to create a Calculated Field.
2. A good use-case for a calculated field
Here is an example. Our pivot table shows the Total Gross Revenue as well as the Total Budget for each Certification or Rating. What if we want to see which ratings are the most profitable? In this scenario, Profit can be defined as Gross Revenue minus Budget. Gross Revenue is how much the movie earned, and Budget is how much the movie cost to make. We can create a field called "Profit" and add it to our pivot table.
3. Creating a calculated field for profit
Go to the pivot table editor, and click on Add in the values section. At the very bottom, you will see Calculated Field. Click on this, and then you will be asked to enter a formula. In this example, we want to type equals Gross minus Budget. You'll need to make sure that you are typing the names of the fields exactly as they are shown in your dataset. If you spell something wrong, the pivot table will show an error message. If you get it right, then you will see the values automatically populate in your pivot table.
4. Checking the math on the calculated field
Let's check it out to make sure it works. 1-point-5 billion dollars of Gross Revenue minus 1-point-09 billion dollars of Budgeted Expenses equals 448 million dollars of Profit. Perfect! Now, this field will act like any other value in our pivot table. At this point, we can go to the header on the pivot table, double-click, and rename it whatever we'd like.
5. Creating another calculated field
Other math equations work as calculated fields as well. Let's say the movie director has a contract which pays 5 percent of Gross Revenue. We can add another field, and enter Gross times 5 percent. The new field will show up with that calculation.
6. Let's practice!
Let's try a few examples.