Building Formulas for Calculated Columns and Measures

This topic describes the basics of building a formula in PowerPivot for Excel, shows an example of creating a calculated column, and describes how to work with tables. The topic has the following sections:

• Measures and Calculated Columns

• Formula Basics

• Working with Tables and Columns

• Troubleshooting Errors in Formulas

After you read this topic, see the following topics for more information:

• Create a Calculated Column

• Create a Measure

Measures and Calculated Columns

Within a PowerPivot workbook, you can use formulas in calculated columns and measures:

• A calculated column is a column that you add to an existing PowerPivot table. Instead of pasting or importing values in the column, you create a Data Analysis Expressions (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.

For more information, see "Measures and Calculated Columns" in Key Concepts in DAX.

Formula Basics

PowerPivot for Excel provides 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 Getting Started with Data Analysis Expressions (DAX).

Formulas can be complex, but the following table shows basic formulas that could be used in a PowerPivot calculated column.

You can build PowerPivot formulas for calculated columns much as you build formulas in Microsoft Excel. You build formulas for measures by using one of the following dialog boxes: Measure Settings Dialog Box (Standard Aggregation) or Measure Settings Dialog Box (Custom Aggregation).

Use the following steps when you build a formula:

1. Each formula must begin with an equal sign.

2. You can either type or select a function name, or type an expression.

3. Begin to type the first few letters of the function or name you want, and AutoComplete displays a list of available functions, tables, and columns. Press TAB to add an item from the AutoComplete list to the formula.

4. Click the Fx button to display a list of available functions. To select a function from the dropdown list, use the arrow keys to highlight the item, and click OK to add the function to the formula.

5. Supply the arguments to the function by selecting them from a dropdown list of possible tables and columns, or by typing in values.

6. Check for syntax errors: ensure that all parentheses are closed, and that columns, tables and values are referenced correctly.

7. Press ENTER to accept the formula.

   Note

   In a calculated column, as soon as you accept the formula, the column is populated with values. In a measure, pressing ENTER saves the measure definition, and if the measure is a new one, PowerPivot automatically adds the measure to the Values area of the PivotTable.

Create a Simple Formula

The following example shows how to create a calculated column with a simple formula, based on the following data:

Tips for Using AutoComplete

• You can use Formula AutoComplete in the middle of an existing formula with nested functions. The text immediately before the insertion point is used to display values in the drop-down list, and all of the text after the insertion point remains unchanged.

• PowerPivot does not add the closing parenthesis of functions or automatically match parentheses. You must make sure that each function is syntactically correct or you cannot save or use the formula. PowerPivot does highlight parentheses, which makes it easier to check if they are properly closed.

For more information about using AutoComplete, see Create a Calculated Column and Create a Measure.

Working with Tables and Columns

PowerPivot tables look similar to Excel tables, but are different in the way that they work with data and with formulas:

• Formulas work only with tables and columns, not with individual cells, range references, or arrays.

• Formulas can use relationships to get values from related tables. The values that are retrieved are always related to the current row value.

• You cannot paste Data Analysis Expressions (DAX) formulas into an Excel workbook and vice versa.

• You cannot have irregular or "ragged" data, like you do in an Excel worksheet. Each row in a table must contain the same number of columns. However, you can have empty values in some columns. Excel data tables and PowerPivot data tables are not interchangeable, but you can link to Excel tables from PowerPivot and paste Excel data into PowerPivot. For more information, see Add Data by using Excel Linked Tables and Copy and Paste Data.

Referring to Tables and Columns in Formulas and Expressions

You can refer to any table and column by using its name. For example, the following formula illustrates how to refer to columns from two tables by using the fully qualified name:

=SUM('New Sales'[Amount]) + SUM('Past Sales'[Amount])

When a formula is evaluated, PowerPivot for Excel first checks for general syntax, and then checks the names of columns and tables that you provide against possible columns and tables in the current context. If the name is ambiguous or if the column or table cannot be found, you will get an error on your formula (an #ERROR string instead of a data value in cells where the error occurs). For more information about naming requirements for tables, columns, and other objects, see "Naming Requirements" in DAX Syntax Specification.

Table Relationships

Tables can be related to other tables. By creating relationships, you gain the ability to look up data in another table and use related values to perform complex calculations. For example, you can use a calculated column to look up all the shipping records related to the current reseller, and then sum the shipping costs for each. The effect is like a parameterized query: you can calculate a different sum for each row in the current table.

Many DAX functions require that a relationship exist between the tables, or among multiple tables, in order to locate the columns that you have referenced and return results that make sense. Other functions will attempt to identify the relationship; however, for best results you should always create a relationship where possible. For more information, see the following topics:

• Creating Relationships Between Tables

• Understanding the Use of Relationships and Lookups in Formulas

When you work with PivotTables, it is especially important that you connect all the tables that are used in the PivotTable so that the summary data can be calculated correctly. For more information, see Work with Relationships in PivotTables.

Troubleshooting Errors in Formulas

If you get an error when you are defining a calculated column, the formula might contain either a syntactic error or semantic error.

Syntactic errors are the easiest to resolve. They typically involve a missing parenthesis or comma. For help with the syntax of individual functions, see Function Reference (DAX).

The other type of error occurs when the syntax is correct, but the value or the column referenced does not make sense in the context of the formula. Such semantic errors might be caused by any of the following problems:

• The formula refers to a non-existing column, table, or function.

• The formula appears to be correct, but when the PowerPivot data engine fetches the data it finds a type mismatch, and raises an error.

• The formula passes an incorrect number or type of parameters to a function.

• The formula refers to a different column that has an error, and therefore its values are invalid.

• The formula refers to a column that has not been processed. This can happen if you changed the workbook to manual mode, made changes, and then never refreshed the data or updated the calculations.

In the first four cases, DAX flags the entire column that contains the invalid formula. In the last case, DAX grays out the column to indicate that the column is in an unprocessed state.

See Also

Other Resources

Creating and Working with Calculations

Key Concepts in DAX

Understanding Aggregations in Formulas