Edit or Rename a Measure in a PivotTable or PivotChart

Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.

You can modify a measure by changing its aggregation or formula, or by renaming it.

Finding Measures

Before you can view or modify a measure, you must first locate the measure in a table that contains it. All measures that you create are associated with a single table that provides a location for that measure within the workbook. This is true for both implicit measures that are generated for you, and explicit measures that you create manually.

To find a measure, you can browse the contents of each table in the PowerPivot Field List. Similarly, you can view the Calculation Area at the bottom of each table in the PowerPivot window to view the measures associated with each table.

A quicker approach, however, is to use the diagram view.

1. In the PowerPivot window, on the Home tab, click Diagram View.

2. Clear the checkboxes to remove columns, KPIs, and hierarchies from the view, leaving just the Measures checkbox selected.

3. Resize the view so that you can see all of the tables on the screen. Tables that are empty do not contain measures; tables that contain items have measures.

4. Right-click a table that contains measures and click Go To. The table opens in data view. Measures will appear in the Calculation Area grid at the bottom of the table. If the grid is not visible in the workspace, click Calculation Area on the ribbon to display it.

Edit an Existing Measure

After you have found the measure, you can view or edit it in the PowerPivot window or Excel. Modifications include changing the formula, renaming the measure, applying a different data format, moving the measure, or changing the table with which the measure is associated.

To view and change an existing measure in the PowerPivot Window

1. In the PowerPivot window, click the Home tab, and select the table that contains the measure.

2. Click Calculation Area, if it is not already selected.

3. Scroll to the column that contains the measure, right-click the measure to change the data format, add a description, hide it from a client application, or create a new KPI based on the measure.

4. To change the formula, select the measure and then edit the formula in the formula bar at the top of the workbook. To learn more about formulas, see Build Formulas for Calculations.

5. To rename the measure, change the name in the formula bar. The name is the text portion of the formula that is before the colon (:) character. For example, in Sum of Total Product Cost:=SUM(FactResellerSales[TotalProductCost]), the measure name is Sum of Total Product Cost.

6. To move a measure, you can cut and paste the formula in the Calculation Area. You can only move it to a different location in the Calculation Area of the table in which it was created. You cannot move a measure to different table in this view; instead use the Measure Settings dialog in the PowerPivot ribbon in Excel to change the association.

To view and change an existing measure in Excel

1. In the Excel window, click anywhere in the PivotTable or PivotChart area to display the PowerPivot Field List. The PowerPivot Field List contains a list of all tables in the current PowerPivot window, including columns of raw data, calculated columns, and any measures that you might have defined. You can right-click on the definition of any measure and select Edit formula to open a dialog box that lets you view and modify the measure definition.

2. In the PowerPivot Field List, locate the table that contains the measure that you created.

   Each table can contain base columns, calculated columns and measures. Measures are indicated by a small calculator icon to the right of the measure name.

   Right-click the measure, and click Edit Formula.

3. In the Measure Settings dialog box, edit the formula.

   You can also change the measure name or custom name, and the associated table.