How to Add a Calculated Column in Microsoft Power BI
Adding a calculated column in Microsoft Power BI can return useful table-level information. Here’s how.
Not all the information you need is in a spreadsheet. For example, if you want to know the profit of a product you sell, that value is probably not stored at the table level. Instead, you must use an expression that subtracts the cost of manufacturing and/or distribution of the product from the price of the item. Normally one does not store the result of a calculation in a spreadsheet. Instead, 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 isn’t otherwise stored at the table level. You can then add the column to visualizations or create new visualizations based on the new column.
I’m using Power BI Desktop on a Windows 10 64-bit system, but you can also use the Power BI service. You can download the AdventureWorks Sales demonstration .pbix file from GitHub. Once downloaded, double-click the .pbix file to open it in Power BI and follow the instructions, or use your own .pbix file. If you want a quick look 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 metrics, you might be wondering about the difference between them and calculated columns. Both are based on Data Analysis Expressions (DAX). The main difference is that you typically add a measure to a visualization by adding it to the value 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 metrics for complex calculations in Microsoft Power BI Desktop (TechRepublic)
Calculated columns and measures may seem interchangeable, and sometimes it doesn’t matter. When deciding what to use, context is the key factor:
- Power BI uses metrics 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 only evaluates values within the same row. There is no aggregation function. Power BI adds the resulting values to the model and calculates them before activating a filter.
Now that you have an idea of what computed 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 when working with more than one table. Sometimes all of these values are in the same table, so the relationship doesn’t matter. We use a calculated column to return a simple profit margin based on two fields in the products table of the .pbix demonstration file. Applying this to your own work, be sure to look for relationships in the model window when working with two or more tables.
To add a calculated column to the Products table:
- In the Fields pane, right-click Products and select New Column. By default, Power BI names the new column “Column” and Power BI opens the formula bar in response.
- Type in the formula bar
Simple Profit Margin = Product[List Price] - Product[Standard Cost].Overwrite the default “Column =” text. - Click the check mark on the left to add the new column Figure A.
Figure A
Double-click the field or drag it into 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 calculates the profit margin before the user clicks a filter. Additionally, Power BI stores the profit values in the model so they are available for other visualizations.
Figure B
Add a calculated percentage column in Microsoft Power BI
The calculated “Simple Profit Margin” column does not take into account applied discounts and many other factors. As said, it is a “simple” profit margin. As it is, showing these values doesn’t help us much, but a percentage would.
Let’s add another calculated column that returns the percentage of profit for each product. It’s more helpful to see simple win as a percentage. To do this, repeat the above process 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, in the Fields area, select Simple Profit Percentage, and then on the Column Tools tab, in the Formatting group, select Percentage from the Format drop-down list.
Adding this column may change the sort order, but don’t worry about it. Both calculated columns are simple but return useful information. Profit margins range from 23% to over 64%. This is much better information than the simple profit returned by the first column calculated. Fortunately, Power BI can handle much more complex formulas.
When applying calculated columns to your own work, remember that the fields in the formula must be in the same table or in related tables.