Power Pivot Principles: Calculated Columns

Welcome back to our Power Pivot blog.  Today, we consider how to create a calculated column.

A calculated column is a simple formulaic calculation applied to an entire column that remains in the table you created it in.  It can be later used by measures and other calculated columns to create more complex expressions.

Example 

Let’s go through an example to walk through how to add a calculated column into the Power Pivot model.  

Let’s say we want to add a column into the model which will calculate ‘Gross Profit’. Next to the last column of data in your Power Pivot model will be a blank column with the title ‘Add Column’.

Double click on the heading and type ‘Gross Profit’, note, ensure all columns in your Power Pivot data model have a unique name.  In the formula bar, you can type out the formula shown below or by selecting the columns of data you wish to use in the formula:

The new calculated column will appear in the data model as follows:

As this new calculated column contains a formula, it will be computed for each row in the data set.  When the underlying data is refreshed the columns are recalculated each time.  

This column may now be used in another calculated column, measure, chart, and even PivotTable like any other column in your data model.

Another useful calculated column to have might be a year column.  To create this, add a new column, rename it ‘Year’ and use the YEAR function:

That’s it for this week. Stay tuned to our blog page for more on Power Pivot. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.