Calculated Values

Overview

Calculated Values are calculations based on other value fields. It's best to explain them using an example.

Adding a Calculated Value

In our example, we have a pivot view based on an employee table. The pivot groups the data by department and shows the department names on the left (Row Labels). The view has three value fields:

1. Employees – a count of rows. It shows the number of employees in each department.
2. Salaries – a sum of the Salary field. It shows the total salaries for each department.
3. Bonus – a sum of the Bonus field. It shows the total bonuses for each department.

Now suppose that we wanted to show the bonuses as a percent of the salaries. In other words, we'd like to show the ratio between bonuses and salaries for each department. Moreover, we want to sort the data such that the department that pays the highest bonuses as a percentage of salary at the top.

To calculate the "Bonus %"--the ratio between the bonuses and salaries, represented as a percentage, we'll add a Calculated Value. To add a calculated value, press the calculator button next to the Values section of the Fields dialog.

When you press this button the Add Calculated Value dialog shows up. The picture below shows the dialog with the calculation already entered, but we'll explain each step below.

Label

This is the name/label of the calculated field we're creating. To give it a short but descriptive name, we'll call it "Bonus %".

Calculation

You can type the calculation directly, or use the various button. For example, to enter the above calculation you could:

1. Type 100
2. Press the (multiply) button
3. Select "Bonus" from the drop-down list of available value fields (on the right) and press the (Add selected) button.
4. Press the divide button
5. Select "Salaries" from the drop-down list and press the Add selected button.

The calculation consists of numbers (e.g., 100), operator (e.g., multiple), fields (e.g., "Salaries") and parenthesis. You can check that the syntax of your calculation is valid using the "validate" button. When selecting value fields, you can select other calculated fields as well. This allows you to build calculations based on previously defined calculations.

This calculation in our example above will give us to percentage that we wanted. Click OK. You should now see this:

Sorting by Calculated Value

To sort the data such that the department that pays the highest bonuses as a percentage of salary is at the top, simply click in that column and select Sort "Bonus %" largest to smallest.

Hiding Value Fields

Now that we're showing the Bonus as a percentage, perhaps we no longer need to show the bonus. We can now hide the "Bonus" value field. Note that we can't remove it because it's used in the calculation, but hiding it will remove it from view.

To hide the Bonus field, we'll use the field settings dialog. To bring this dialog up, select the Bonus field in the Values list by clicking on it. It should highlight. You can then press the pencil button to get the dialog. (Another way to bring up the dialog is to double-click the Bonus field is the list).

The dialog is shown below

To hide "Bonus" simply check the "Hidden" box in the dialog.

Automatic Formatting

A format will automatically be applies to the calculation as follows:

• Duration – if the calculation involved a value field of type duration (time duration). This is not common.
• Integer – if the calculation involves nothing but integers and does not include division.
• Decimal – if the calculation involves decimal values or division. The decimal will be shows with two decimal places.

Display As

The "Display As" feature of value fields in pivot and chart views applies to calculated values as well.

Totals

The image below shows our pivot with the calculated "Bonus %", but this time with column totals. You should note that the total for "Bonus %" is not a simple sum of the "Bonus %" values above it. That would not make good sense. In fact, the way that the total for "Bonus %" is calculated, is by repeating the calculation for the totals row. We calculate the ratio between the grand total of bonuses to the grand total of salaries.