Examples of Calculated Measures

The following examples show how you can use calculated measures in your pivot tables and charts.

Year over Year Growth

You can create a calculated measure to compare the year over year difference for your sales reps’ performance. The following steps show you how to create a pivot table with a calculated measure that returns this difference.

Step	Description
Step 1: Create two formula fields using the Sales (Ordered) dataset	To calculate the year over year variance, you need to split the sales amount into two separate fields in the dataset. You can create the two following formula fields: Amount LYTD - The Amount Last Year to Date field returns the sales amounts from all of last year up until today's date. Amount TYTD - The Amount This Year to Date field returns the sales amounts for transactions created from the beginning of this year to today's date. To copy and paste the formulas, see Calculating Amounts for Relative Date Ranges.
Step 2: Create your pivot table	Set the following fields to the corresponding section of the Layout panel: Rows: Sales Rep Columns: None Measures: Amount TYTD (Sum) and Amount LYTD (Sum) For information about how to create pivot tables, see Workbook Pivot Tables.
Step 3: Create your calculated measure	Use the following definition: `(Amount TYTD (Sum) - Amount LYTD (Sum)) / Amount LYTD (Sum)` For information about how to create calculated measures, see Working with Calculated Measures.
Step 4: Format the results	You can also customize your pivot table results, add totals and grand totals, or set the decimal values to percentage values. For more information, see Pivot Table Customization.

Step

Description

Step 1: Create two formula fields using the Sales (Ordered) dataset

To calculate the year over year variance, you need to split the sales amount into two separate fields in the dataset. You can create the two following formula fields:

Amount LYTD - The Amount Last Year to Date field returns the sales amounts from all of last year up until today's date.
Amount TYTD - The Amount This Year to Date field returns the sales amounts for transactions created from the beginning of this year to today's date.

To copy and paste the formulas, see Calculating Amounts for Relative Date Ranges.

Step 2: Create your pivot table

Set the following fields to the corresponding section of the Layout panel:

Rows: Sales Rep
Columns: None
Measures: Amount TYTD (Sum) and Amount LYTD (Sum)

For information about how to create pivot tables, see Workbook Pivot Tables.

Step 3: Create your calculated measure

Use the following definition:

(Amount TYTD (Sum) - Amount LYTD (Sum)) / Amount LYTD (Sum)

For information about how to create calculated measures, see Working with Calculated Measures.

Step 4: Format the results

You can also customize your pivot table results, add totals and grand totals, or set the decimal values to percentage values. For more information, see Pivot Table Customization.

After you create the calculated measure, the pivot table displays the difference over time.

Pivot table with calculated measure included.

For more information about comparing sales amounts over time using calculated measures, see the following video:

Calculated Measures and Conditional Formatting

Year over Year Growth

Related Topics