Troubleshoot Recalculation
Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.
This section provides additional technical tips for consideration when planning when to recalculate a workbook. For general information about recalculating and refreshing data in your workbook, see the following topics:
Different Ways to Update Data in PowerPivot
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 PowerPivot workbook, PowerPivot for Excel performs an analysis of the existing PowerPivot 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.
PowerPivot 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, PowerPivot for Excel 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, PowerPivot 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 measure.
See Also
Concepts
Add Calculations to Your Reports, Charts, and PivotTables