Calculated Columns
Contents
Overview
A calculated column is a pivot column that displays data that's computed based on other columns. It's based to explain it using an example.
Adding a Calculated Column
In our example, we have a Status field with values of Completed, Refunded, and Reversed. The Status field is shown in the columns.
The calculated column that we want to create will show the returns (refunded plus reversed) as a percentage of the completed payments.
Note that the difference between a calculated column that we'll be doing here and a Calculated Values is that the columns here correspond to column labels (different statuses) rather than numeric fields from the table (such as the Amount field). This is a subtle but important distinction.
The picture below shows the pivot view before adding the calculated column.
Now let's add the calculated column to the Status field. In effect, we'll be adding a fourth status that's calculated based on the existing 3 values (Completed, Refunded, and Reversed).
We'll click on the Status field under Column Labels. This will highlight it and enable the pencil icon that calls up the settings dialog for this field.
When the settings dialog comes up, click on the "Calculated" button.
The Calculated columns list for "Status" comes up. The list is empty because we're adding our very first calculated column, so click Add...
The Add Calculated Column dialog comes up. We'll show it with the calculation already entered, but we'll explain each step.
Label
This is the label (column heading) that we're creating. To give it a short but descriptive name we'll call it "% Returns".
Calculation
You can type the calculation directly, or use the various button. See Calculated Values for more explanation of the various buttons in the calculation editor. The editor works very similarly to that of the Calculated Value.
Our calculation adds the refunds and reversed, divides by completed to get the ratio and multiplies by negative 100 to get the result as a positive percentage.
Placement
The dialog gives you full control over the placement of the calculated column. For our example, we want to show it at the end, after the other three status values.
Click OK. The results are shown below.
A new column is displayed on the right showing the % returns.
Calculation involving Missing Values
In our example, you can see that for some product sub categories there are no reversed payments and the Reversed column is showing blank. In the calculation, such blank columns are always taken as zero. This is why you see the value 0.00 as the % Returns.
Multiple Value Fields
In our example, we had only one value field, namely Amount. If we had multiple values, for example the count and the amount, then the calculation will be repeated for each. If you want to do the calculation only for the amount, then you have that option. See the example below:
In the lower part of the dialog we're informed that there are multiple value fields and are given a choice. We chose to perform the calculation for a selected value field, namely Amount.
Subtotals and Grand Totals
These are supported when applied vertically as in our example. You can see that % Returns was calculated for the grand total as well. For horizontal subtotals and grand totals, we skip the calculated column. You can see in our example that it makes sense to do so. In general, skipping calculated columns avoids double counting.
When a calculated column uses a calculated value field, it may be unclear whether a subtotal would be calculated by the calculated column or the calculated value. In such case, the calculated column prevails.
Value Field Display As
Display As options that apply vertically or reference a specific base item are fully supported. Other options are applied only to non-calculated columns. The reason is that including the calculated columns often leads to double counting or incompatibilities such as comparing a ratio to a total.
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.
