How to add a calculated column in Microsoft Power BI

Adding a calculated column in Microsoft Power BI can return helpful information at the table level. Here’s how.

Not every piece of information you need will be in a table. For instance, if you want to know the profit of a product you sell, that value probably isn’t stored at the table level. Instead, you must use an expression that subtracts the cost of manufacturing and/or distributing the product from the price of the item. Normally, you don’t store the result of a calculation in a table. Rather, you use an expression to return the profit when needed.

In this tutorial, I’ll show you how to create a calculated column to return important information that’s not otherwise stored at the table level. You can then add the column to visualizations or create new ones based on the new column.

I’m using Power BI Desktop on a Windows 10 64-bit system, but you can also use Power BI Service. You can download the demonstration .pbix file, AdventureWorks Sales from GitHub. Once downloaded, double-click the .pbix file to open it in Power BI and follow along or use a .pbix file of your own. If you want a sneak peek at the final results, check out this demo file.

How to decide between a calculated column and a measure in Power BI

If you’re familiar with Power BI measures, you might be wondering about the difference between those and calculated columns. Both are based on Data Analysis Expressions (DAX). The main difference is that you usually add a measure to a visualization by adding it to the Values bucket. In contrast, a calculated column is a new field that can be added to rows, axes, legends and groups.

SEE: How to add quick measures for complex calculations in Microsoft Power BI Desktop (TechRepublic)

Calculated columns and measures might seem interchangeable, and sometimes it won’t matter. When deciding which to use, context is the determining factor:

• Power BI uses measures with visualizations and updates them after a filter is applied. The formula often includes an aggregate function to evaluate groups.
• Power BI applies a calculated column expression to all rows in the table but evaluates only values within the same row. There’s no aggregating function. Power BI adds the resulting values to the model and calculates it before a filter is engaged.

Now that you’ve got an idea of what calculated columns can do, let’s create one.

How to add a calculated column in Power BI

When adding a calculated column, you must use related data if you’re working with more than one table. Sometimes all of those values will be in the same table, so the relationship won’t be a factor. We’ll use a calculated column to return a simple profit margin based on two fields in the demonstration .pbix file’s Products table. When applying this to your own work, be sure to check for Relationships in the Model window if you’re working with two or more tables.

To add a calculated column to the Products table:

1. Right-click Products in the Fields pane, and choose New Column. Power BI names the new column “Column,” by default, and Power BI will open the formula bar in response.
2. In the formula bar, enter Simple Profit Margin = Product[List Price] - Product[Standard Cost]. Overwrite the default “Column =” text.
3. Click the checkmark to the left to add the new column Figure A.

Figure A

Double-click the field or drag it to the Rows bucket to update the visualization. Remember, if you base a visualization on the Products table and add the new column to it, Power BI will calculate the profit margin before the user clicks a filter. In addition, Power BI stores the profit values in the model, so they are available to other visualizations.

Figure B

Adding a calculated column with a percentage in Microsoft Power BI

The Simple Profit Margin calculated column doesn’t take into consideration any applied discounts and many other factors. It is, as named, a “simple” profit margin. As it is, viewing these values doesn’t help us much, but a percentage would.

Let’s add another calculated column that will return the percentage of profit for each product. Seeing the simple profit as a percentage will be more helpful. To do so, repeat the process above, using the formula shown in Figure C:

Simple Profit Percentage = ('Product'[List Price] - 'Product'[Standard Cost]) / 'Product'[List Price]

Figure C

To change the format for this column from currency to percentage, choose Simple Profit Percentage in the Fields pane, and then, choose Percentage from the Format dropdown in the Formatting group on the Column Tools tab.

The addition of this column may change the sort order, but don’t worry about it. Both calculated columns are simple in structure, but they return helpful information. Profit margins run from 23% to over 64%. This is much better information than the simple profit returned by the first calculated column. Fortunately, Power BI can handle much more complex formulas.

Don’t forget, when applying calculated columns to your own work, that the fields in the formula must be in the same table or in related tables.