Share via

Calculated Columns

  • Article

Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.

This topic shows you how to create a calculated column based on data in the DAX sample workbook, and includes information about using AutoComplete when building a formula. For more information about formulas, see Build Formulas for Calculations.

Understanding Calculated Columns

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 DAX formula that defines the column values. The calculated column can be used in a PivotTable, PivotChart, or Power View report as you would any other data column.

The formulas in calculated columns are much like the formulas that you create in Excel. Unlike in Excel, however, you cannot create different formulas for different rows in a table; instead, the DAX formula is automatically applied to the entire column.

When a column contains a formula, the value is computed for each row. The results are calculated for the column as soon as you create the formula. Column values are then recalculated as necessary, such as when the underlying data is refreshed.

You can create calculated columns that are based on measures and other calculated columns. For example, you might create one calculated column to extract a number from a string of text, and then use that number in another calculated column.

Creating a Calculated Column

A calculated column is based on data that you have added into an existing table. For example, you might choose to concatenate values, perform addition, extract substrings, or compare the values in other fields. To add a calculated column, you must have already added at least one table in your PowerPivot workbook.

This example demonstrates the use of AutoComplete to build a simple formula for use in a new calculated column. The formula is as follows:

=EOMONTH([StartDate],0])

The formula extracts the month from the StartDate column in the Promotion table in the DAX sample workbook. It then calculates the end of the month value for each row in the Promotion table. The second parameter specifies the number of months before or after the month in StartDate; in this case, 0 means the same month. For example, if the value in the StartDate column is 6/1/2001, the value in the calculated column will be 6/30/2001.

For information about the sample workbook, see Get Sample Data for PowerPivot.

Note

In Windows Vista and Windows 7, features in the PowerPivot window are available on a ribbon, which is discussed in this topic. In Windows XP, features are available from a set of menus. If you are using Windows XP and want to see how the menu commands relate to the ribbon commands, see The PowerPivot UI in Windows XP.

To create a calculated column using AutoComplete

  1. In the PowerPivot window, click the tab that contains the Promotion table. To see the Promotion tab, you might have to click the down arrow to the right of the tabs that are displayed.

  2. In the PowerPivot window, on the Design tab, in the Columns group, click Add.

    Add Column is highlighted over the empty rightmost column, and the cursor moves to the formula bar.

  3. Type an equal sign, and then click the function button (fx).

  4. EOMONTH is a date and time function, so select Date & Time from the Select a category dropdown list.

    The DAX functions that are available from the Insert Function dialog box are grouped by category. You can view the complete list of available functions by selecting All.

    • Press TAB to move between the dropdown list of function categories, the dropdown list of functions, and the OK and Cancel buttons.

    • Press the up and down arrow keys to select a category of functions or an individual function.

    • As you select each function, PowerPivot displays help for the function, including a description and list of the required and optional arguments.

  5. In the Select a function dropdown list, select EOMONTH, and click OK.

    The Formula Bar is updated to show the function and the opening parenthesis, and the cursor is automatically positioned at the place where you will type the next argument:

    =EOMONTH(

    The tooltip beneath the Formula Bar indicates that the EOMONTH function requires two arguments, the first a date, and the second a numeric value that represents the number of months.

  6. Type a left bracket [, to display a list of columns from the current table.

    Tip: If the column is in another table, type the first few letters of the name of the table that the column belongs to, and then select the fully qualified column name from the list.

    For this example, select [StartDate] from the list, and press TAB.

    The column name is inserted into the formula as follows:

    =EOMONTH ([StartDate]

  7. Type a comma, the value 0, and then the closing parenthesis.

    The final formula should be as follows:

    =EOMONTH([StartDate], 0)

  8. Press ENTER to accept the formula.

    The entire column is populated with the formula, and a value is calculated for each row.

Naming a Calculated Column

By default new calculated columns are added to the right of other columns in a worksheet, and the column is automatically assigned the default name of CalculatedColumn1, CalculatedColumn2, and so forth. You can rearrange and rename columns after they are created. Be aware of the following restrictions on changes to calculated columns:

  • Each column name must be unique within a table.

  • Avoid names that have already been used for measures within the same workbook. Although it is possible for a measure and a calculated column to have the same name, if names are not unique you can easily get calculation errors. To avoid accidentally invoking a measure, when referring to a column always use a fully qualified column reference.

  • When you rename a calculated column, any formulas that rely on the existing column must be updated. Unless you are in manual update mode, updating the results of formulas takes place automatically. However, this operation might take some time.

  • There are some characters that cannot be used within the names of columns, or in the names of objects in a PowerPivot workbook. For more information, see "Naming Requirements" in DAX Syntax Specification for PowerPivot.

To rename or edit an existing calculated column

  1. In the PowerPivot window, right-click the heading of the calculated column that you want to rename, and click Rename Column.

  2. Type a new name, and then press ENTER to accept the new name.

Changing the Data Type

You can change the data type for a calculated column in the same way you can change the data type for all non-calculated columns. You cannot make the following data-type changes: from text to decimal, from text to integer, from text to currency, and from text to date. You can make a change from text to Boolean.

Understanding the Performance of Calculated Columns

The formula for a calculated column can be more resource-intensive than the formula used for a measure. One reason is that the result for a calculated column is always calculated for each row in a table, whereas a measure is only calculated for the cells that are used in the PivotTable or PivotChart.

For example, a table with a million rows will always have a calculated column with a million results, and a corresponding effect on performance. However, a PivotTable generally filters data by applying row and column headings; therefore, the measure is calculated only for the subset of data in each cell of the PivotTable.

A formula has dependencies on the objects that are referenced in the formula, such as other columns or expressions that evaluate values. For example, a calculated column that is based on another column, or a calculation that contains an expression with a column reference, cannot be evaluated until the other column is evaluated. By default, automatic refresh is enabled in workbooks; therefore, all such dependencies can affect performance while values are updated and formulas refreshed.

To avoid performance issues when you create calculated columns, follow these guidelines:

  • Rather than create a single formula that contains many complex dependencies, create the formulas in steps, with results saved to columns, so that you can validate the results and assess performance.

  • Modification of data will often require that calculated columns be recalculated. You can prevent this by setting the recalculation mode to manual; however, if any values in the calculated column are incorrect the column will be grayed out, until you refresh and recalculate the data.

  • If you change or delete relationships between tables, formulas that use columns in those tables will become invalid.

  • If you create a formula that contains a circular or self-referencing dependency, an error will occur.

See Also

Concepts

Add Calculations to Your Reports, Charts, and PivotTables

Build Formulas for Calculations

Data Analysis Expressions (DAX) Overview

Aggregations in Formulas

Other Resources

Relationships Between Tables