Calculations in Power Pivot

Many data analysis and modeling problems in Power Pivot can be solved by using calculations. There are two types of calculations in Power Pivot, calculated columns and calculated fields. Both types of calculations use formulas. In many cases, for example, when creating a calculated field by using the AutoSum feature with a standard aggregation function such as SUM or AVERAGE, you do not have to create a formula at all. The formula is created automatically for you by Power Pivot. In other cases, you might have to create a custom formula yourself. In Power Pivot, you create custom calculation formulas by using Data Analysis Expressions (DAX).

DAX formulas in Power Pivot are very similar to Excel formulas. In fact, DAX uses many of the same functions, operators, and syntax as Excel formulas. However, DAX also has additional functions designed to work with relational data and perform more dynamic calculations.

Types of Calculations in Power Pivot

Calculated Columns

With calculated columns, you can add new data to Power Pivot tables. Instead of pasting or importing values into the column, you create a DAX formula that defines the column values. To learn more, see the following articles:

Calculated Columns in Power Pivot

Create a Calculated Column

Calculated Fields

Calculated fields are used in data analysis; for example, sums, averages, minimum or maximum values, counts, or more advanced calculations that you create using a DAX formula. The value of a calculated field always changes in response to selections on rows, columns, and filters, allowing for ad hoc data exploration. To learn more, see the following articles:

Calculated Fields in Power Pivot

Create a Calculated Field

Key Performance Indicators

Key Performance Indicators (KPI) are based on a specific calculated field and designed to help users quickly evaluate the current value and status of a metric against a defined target.

Key Performance Indicators (KPIs) in Power Pivot

Power Pivot Formulas use Data Analysis Expressions (DAX)

DAX is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. Stated more simply, DAX helps you create new information from data already in your data model. To learn more, see the following articles:

Understanding DAX

Data Analysis Expressions (DAX) in Power Pivot

Data Analysis Expressions (DAX) Reference

DAX Scenarios in Power Pivot

Recalculate Formulas in Power Pivot

Learn how to use DAX

QuickStart: Learn DAX Basics in 30 Minutes

Create Formulas for Calculations

DAX Functions

Types of DAX Functions

DAX Function Reference (DAX)

Advanced Formulas

Context in Power Pivot (DAX) Formulas

Lookups in Power Pivot Formulas

Aggregations in Power Pivot

Filter Data in DAX Formulas

Dates in Power Pivot

Other DAX Resources

DAX Resource Center Wiki (TechNet)

 
 
Applies to:
Excel 2013, Power Pivot in Excel 2013