Recalculate Formulas in Power Pivot

When you are working with data in Power Pivot, from time to time you might need to refresh the data from the source, recalculate the formulas that you have created in calculated columns, or make sure data presented in a PivotTable is up-to date.

This topic explains the difference between refreshing data vs. recalculating data, provides an overview of how recalculation is triggered, and describes your options for controlling recalculation.

Understanding Data Refresh vs. Recalculation

Power Pivot uses both data refresh and recalculation:

Data refresh means obtaining up-to-date data from external data sources. Power Pivot does not automatically detect changes in external data sources, but data can be refreshed manually from the Power Pivot window or automatically if the workbook is shared on SharePoint.

Recalculation means updating all the columns, tables, charts, and PivotTables in your workbook that contain formulas. Because recalculation of a formula incurs a performance cost, it is important to understand the dependencies associated with each calculation.

Important:  You shouldn't save or publish the workbook until the formulas in it have been recalculated.

Manual vs. Automatic Recalculation

By default, Power Pivot automatically recalculates as required while optimizing the time required for processing. Although recalculation can take time, it is an important task, because during recalculation, column dependencies are checked, and you will be notified if a column has changed, if the data is invalid, or if an error has appeared in a formula that used to work. However, you can choose to forego validation and only update calculations manually, especially if you are working with complex formulas or very large data sets and want to control the timing of updates.

Both manual and automatic modes have advantages; however, we strongly recommend that you use automatic recalculation mode. This mode keeps the Power Pivot metadata in synch, and prevents problems caused by deletion of data, changes in names or data types, or missing dependencies. 

Using Automatic Recalculation

When you use automatic recalculation mode, any changes to data that would cause the result of any formula to change will trigger recalculation of the entire column that contains a formula. The following changes always require recalculation of formulas:

• Values from an external data source have been refreshed.

• The definition of the formula changed.

• Names of tables or columns that are referenced in a formula have been changed.

• Relationships between tables have been added, modified, or deleted.

• New measures or calculated columns have been added.

• Changes have been made to other formulas within the workbook, so columns or calculations that depend on that calculation should be refreshed.

• Rows have been inserted or deleted.

• You applied a filter that requires execution of a query to update the data set. The filter could have been applied either in a formula or as part of a PivotTable or PivotChart.

Using Manual Recalculation

You can use manual recalculation to avoid incurring the cost of computing formula results until you are ready. Manual mode is particularly useful in these situations:

• You are designing a formula by using a template and want to change the names of the columns and tables used in the formula before you validate it.

• You know that some data in the workbook has changed but you are working with a different column that has not changed so you want to postpone a recalculation.

• You are working in a workbook that has many dependencies and want to defer recalculation till you are sure all the necessary changes have been made.

Note that, as long as the workbook is set to manual calculation mode, Power Pivot in Microsoft Excel 2013 does not perform any validation or checking of formulas, with the following results:

• Any new formulas that you add to the workbook will be flagged as containing an error.

• No results will appear in new calculated columns.

To configure the workbook for manual recalculation

1. In Power Pivot, click Design> Calculations> Calculation Options> Manual Calculation Mode.

2. To recalculate all tables, click Calculation Options> Calculate Now.

   Formulas in the workbook are checked for errors, and tables are updated with results, if any. Depending on the amount of data and the number of calculations, the workbook may become unresponsive for some time.

Important:  Before you publish the workbook you should always change the calculation mode back to automatic. This will help prevent problems when designing formulas.

Troubleshooting Recalculation

Dependencies

When a column depends on another column, and the contents of that other column change in any way, all related columns might need to be recalculated. Whenever changes are made to the Power Pivot workbook, Power Pivot in Microsoft Excel 2013 performs an analysis of the existing Power Pivot data to determine whether recalculation is required, and performs the update in the most efficient way possible.

For example, suppose you have a table, Sales, that is related to the tables, Product and ProductCategory; and formulas in the Sales table depend on both of the other tables. Any change to either the Product or ProductCategory tables will cause all calculated columns in the Sales table to be recalculated. This makes sense when you consider that you might have formulas that roll up sales by category or by product. Therefore, to be sure the results are correct; the formulas based on the data must be recalculated.

Power Pivot always performs a complete recalculation for a table, because a complete recalculation is more efficient than checking for changed values. The changes that trigger recalculation might include such major changes as deleting a column, changing the numeric data type of a column, or adding a new column. However, seemingly trivial changes, such as changing the name of a column, might trigger recalculation as well. This is because the names of columns are used as identifiers in formulas.

In some cases, Power Pivot may determine that columns can be excluded from recalculation. For example, if you have a formula that looks up a value such as [Product Color] from the Products table, and the column that is altered is [Quantity] in the Sales table, the formula does not need to be recalculated even though the tables Sales and Products are related. However, if you have any formulas that rely on Sales[Quantity], recalculation is required.

Sequence of Recalculation for Dependent Columns

Dependencies are calculated prior to any recalculation. If there are multiple columns that depend on each other, Power Pivot follows the sequence of dependencies. This ensures that the columns are processed in the right order at the maximum speed.

Transactions

Operations that recalculate or refresh data take place as a transaction. This means that if any part of the refresh operation fails, the remaining operations are rolled back. This is to ensure that data is not left in a partially processed state. You cannot manage the transactions as you do in a relational database, or create checkpoints.

Recalculation of Volatile Functions

Some functions such as NOW, RAND, or TODAY, do not have fixed values. To avoid performance problems, execution of a query or filtering will usually not cause such functions to be re-evaluated if they are used in a calculated column. The results for these functions are only recalculated when the entire column is recalculated. These situations include refresh from an external data source or manual editing of data that causes re-evaluation of formulas that contain these functions. However, volatile functions such as NOW, RAND, or TODAY will always be recalculated if the function is used in the definition of a Calculated Field.