Overview of Adding Calculations in PowerPivot
After you have imported data into your workbook, you can add calculations to extend, combine, and summarize information. PowerPivot for Excel provides Data Analysis Expressions (DAX), a new formula language for creating custom calculations. DAX enables users to define custom calculations in PowerPivot tables and in Excel PivotTables. DAX includes some of the functions that are used in Excel formulas, and additional functions that are designed to work with relational data and perform dynamic aggregation. For more information, see Data Analysis Expressions (DAX) Overview.
Within a PowerPivot workbook, the calculations that you can create are called calculated columns and measures:
A calculated column is a column that you add to an existing PowerPivot table. Instead of typing, pasting or importing values in the column, you create a DAX formula that defines the column values. If you include the PowerPivot table in a PivotTable (or PivotChart), the calculated column can be used as you would any other data column.
A measure is a formula that is created specifically for use in a PivotTable (or PivotChart) that uses PowerPivot data. Measures can be based on standard aggregation functions, such as COUNT or SUM, or you can define your own formula by using DAX. A measure is used in the Values area of a PivotTable. If you want to place calculated results in a different area of a PivotTable, use a calculated column instead.
The following topics provide more information about how to build and use calculations.