Working with Calculated Measures
Before you create a calculated measure, you should consider the fields and operators you want to include in the calculation. To learn about the type of fields and basic operators that you can use in your calculations, see Supported Calculations.
You can define your calculated measure using fields from the dataset connected to the workbook visualization you are in. In visualizations based on linked datasets, this includes any combination of fields from either dataset. If the fields you want to use to build your measure are not available, edit the connected datasets or contact the dataset owner if you are not the original author. For more information, see Editing a Dataset.
After you create a calculated measure, you can use it to sort and filter your results. Calculated measures support all sorting and filtering options available for base measures. You can also change the format of a calculated measure’s numeric values, add totals and grand totals, and apply conditional formatting. For more information about these options, see Workbook Visualization Filters, Pivot Table Customization, and Conditional Formatting.
To open the calculated measure editor, click Create Calculated Measure in the Layout panel of any pivot table or chart. You can create as many calculated measures as you need, however you can only use them in the workbook visualization where you created them. If you want to use the same calculated measure in other visualizations, copy and paste the measure definition.
To learn how to work with calculated measures, see the following:
Creating Calculated Measures
When you create a calculated measure, you define the fields and basic operators of your calculation. All calculated measures must contain a name and a valid definition. As you create your calculated measure, a message at the bottom of the editor indicates if the definition is valid. If the definition is not valid, an error icon appears next to the line that contains validation errors. Hover over the error icon to see how to correct the definition.
After you create your measure, you can edit and delete it from the Layout panel of the chart or pivot table.
To create a calculated measure:
-
On a pivot table or chart, click Create Calculated Measure from the Layout panel. Alternatively, from within a pivot table, click the Field Menu icon
next to the field that you want to use in your calculated measure and select Create Calculated Measure.The calculated measure editor appears with instructions on how to define your calculations.
-
In the Name field, enter the name of your calculated measure.
-
Add fields to the editor:
-
Drag fields from the Dataset Panel to the Definition field. In visualizations based on linked datasets, you can switch between the fields within each dataset by clicking the dataset name.
-
In the Definition field, start typing the field names.
Fields that you use in your calculated measure are highlighted in blue in the Dataset panel.
-
-
Add calculation operators:
-
In the Definition field, type a basic operator such as plus (+), minus (-), multiplied by (*), divided by (/), or parenthesis ().
-
Press Ctrl + Space to display all options and select an operator.
-
-
(Optional) To make changes to one of the fields used in the definition, click the Field Menu icon
. The following options appear:-
If you want to change the aggregate function of the field, select a different function from the list.
-
If you are working with fields that have values in multiple currencies, select Currency... to view the consolidation and conversion options. For more information, see Currency Conversion in Workbook.
-
-
(Optional) To remove a field from the definition, place the cursor next to the field and press Backspace, or click the Field Menu icon and select Remove.
-
Click Apply to add the calculated measure to your pivot table or chart.
Your calculated measure appears in the list of measures in the Layout panel with a Calculator icon
. -
On your pivot table or chart, click the Refresh icon
to see the results of the calculated measure in the viewer.
Calculated Measure Editor
The Calculated Measure editor is where you define new calculated measures.
The elements of the editor are identified in the image below:
|
1 |
Workbook Visualization Name - Displays the name of the visualization where you are creating the calculated measure. |
|
2 |
Dataset Panel - Contains all of the fields included in the connected dataset. In visualizations based on linked datasets, both datasets are shown, enabling you to switch between them. To view additional information about a field, point to the field and click the Information icon |
|
3 |
Calculated Measure Name - Type the name of your calculated measure. |
|
4 |
Definition Editor - Create your calculations with the fields available in the Dataset Panel and basic operators. To create your calculations, you can do the following:
|
|
5 |
Validation Message - Displays a message that the definition is valid or that the definition contains errors. If the definition is not valid, an error icon appears next to the line that contains validation errors. Hover over the error icon to see how to correct the definition. |
|
6 |
Editor Menu - You can do the following:
|
on the right.