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 measures. Both types of calculations use formulas. In many cases, for example, when creating a measure 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:
Measures
Measures 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 measure always changes in response to selections on rows, columns, and filters, allowing for ad hoc data exploration. To learn more, see the following articles:
Key Performance Indicators
Key Performance Indicators (KPI) are based on a specific measure and designed to help users quickly evaluate the current value and status of a metric against a defined target.
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
Learn how to use DAX
DAX Functions
Advanced Formulas
Context in Power Pivot (DAX) Formulas
