Create a Calculated Column (Tutorial)

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

In this lesson you'll create new data in your PowerPivot workbook by adding calculated columns. A calculated column is based on data that already exists in your PowerPivot workbook.

You will create a calculated column named Total Profit based on the existing revenue and cost information in your data. In addition, you will also create calculated columns in the DimProducts table that reference product category values in other tables. Later, you’ll use these related columns in a new hierarchy that includes product category, subcategory, and product names. As you will see, adding columns from related tables improves PivotTable exploration through hierarchies that include fields from other tables. For more information, see Calculated Columns.

Prerequisites

This topic is part of a PowerPivot for Excel tutorial, which should be completed in order. For information about prerequisites and the first step in the tutorial, see PowerPivot for Excel Tutorial Introduction.

Create a calculated column for Total Profit

  1. In the PowerPivot window, switch back to Data View, and then select the FactSales table.

  2. On the Design tab, in the Columns group, click Add.

  3. In the formula bar above the table, type the following formula. AutoComplete helps you type the fully qualified names of columns and tables, and lists the functions that are available. For tips on how to use AutoComplete, see Build Formulas for Calculations.

    =[SalesAmount] - [TotalCost] - [ReturnAmount].

  4. When you have finished building the formula, press ENTER to accept the formula.

    Values are populated for all the rows in the calculated column. If you scroll down through the table, you will see that rows can have different values for this column, based on the data that is in each row.

  5. Rename the column by right-clicking CalculatedColumn1 and selecting Rename Column. Type TotalProfit, and then press ENTER.

  1. In the PowerPivot window, in Data View, select the DimProduct table.

  2. On the Design tab, in the Columns group, click Add.

  3. In the formula bar above the table, type the following formula. The RELATED function returns a value from a related table. In this case, the ProductCategory table includes the names of product categories, which will be useful to have in the DimProduct table when you build a hierarchy that includes category information. For more information about this function, see RELATED Function.

    =RELATED(ProductCategory[ProductCategoryName]).

  4. When you have finished building the formula, press ENTER to accept the formula.

    Values are populated for all the rows in the calculated column. If you scroll down through the table, you will see that each row now has a Product Category Name.

  5. Rename the column by right-clicking CalculatedColumn1 and selecting Rename Column. Type Product Category, and then press ENTER.

  6. On the Design tab, in the Columns group, click Add.

  7. In the formula bar above the table, type the following formula, and then press ENTER to accept the formula.

    =RELATED(DimProductSubcategory[ProductSubcategoryName]).

  8. Rename the column by right-clicking CalculatedColumn1 and selecting Rename Column. Type Product Subcategory, and then press ENTER.

Next Step

To continue this tutorial, go to the next topic: Create a Hierarchy in a Table (Tutorial).

See Also

Concepts

Build Formulas for Calculations