How to Create a Calculated Column in Power BI (Step-by-Step)
Written by co-founder Kasper Langmann, Microsoft Office Specialist.
A calculated column in Power BI lets you add new data on your table and use them to create visuals.
Though it’s not that difficult, familiarity with DAX (Data Analysis Expression) formulas as well as how to create new fields in Power BI will make this easier.
But if you’re new to Power BI and you’re still learning the ropes, don’t worry. We got you covered too! 👍
In this article, we’ll give you a walkthrough on how to creating a calculated column in Power BI.
Ready? Let’s start! 🙌
*This tutorial is for Power BI Online (also called "Power BI Service").
Table of Content
Introduction to Power BI Calculated Columns
As a business intelligence and analytics solution, Power BI has no problem finding insights about your data and visualizing them.
However, Power BI might not be the best tool for data modeling and authorship.
But that doesn’t mean it’s impossible to do in Power BI. Though not as intuitive compared to the spreadsheet functionality of Excel, you can still manipulate data in Power BI as well as transform data as you need.
However, you can only do this using Power BI Desktop. This is one of the differences between the desktop version vs the online service of Power BI.
This is where calculated columns in Power BI enter. With DAX formulas, you can define a column’s values and use them on reports.
Some of the things you can do with calculated columns include:
- Combining text values from different columns
- Calculating numeric values from other values
Reminder:
In this article, we’ll be using the free retail analysis sample. If you would like to follow along, follow the instructions on our article under “How to Get Sample Data in Power BI Desktop” section.
Creating a Calculated Column
To start, view the ‘Store’ table in data view.
You’ll see all the columns that belong to that table.
As expected, the table contains information about stores like what city they’re located, postal code, store name, selling area size, and others.
Now, let’s say that you would like to create a new column that contains the city where the store is located plus the postal code.
In short, you would like to combine the values within the ‘City’ column and the ‘PostalCode’ column.
To add a new column, right-click on the ‘Store’ field and select ‘New column’:
That will create a new column on the table.
For convenience, give your new column a new name that’s related to the values you would like to put.
Let’s name the column “Location” by changing on the formula bar:
Location =
The next part is telling Power BI to put the values from the ‘City’ and ‘PostalCode’ columns to the new ‘Location’ column.
Again, let’s assume that what you would like to happen is put a comma (,) between the values like in “Weirton, WV, 26032”.
The first step is to refer to the ‘City’ column. Write an opening bracket “[“ on the formula bar.
You would see a suggestion of all the columns that exist on the table. To make it easier, type in “City” and choose the one with “[City]” by pressing the tab or enter key on your keyboard.
Location = [City]
It should now look like this:
To join two text strings together, you would need to use the ‘CONCATENATE’ function.
However, instead of using the DAX code, we’ll be using the ampersand (&) operator.
Since what we would like to happen is [City] + , + [PostalCode], the syntax should be:
Location = [City] & ", " & [PostalCode]
After pressing enter, Power BI will populate your new column with the desired values:
If the values appeared on your end, congratulations! You just made your first calculated column in Power BI. 🎉
Now, we barely used any DAX functions in our example above. But you should know that you can do so much more. Here’s a complete list of DAX expressions from Microsoft.
Using a Calculated Column
Using a newly made calculated column is just like using any pre-existing columns.
After you created the column, it will automatically reflect on the fields pane.
To demonstrate, let’s create a table visual that shows the store name, its store chain type, and the location of the store.
On the report view, click the ‘Table’ icon on the visualizations pane and check ‘Name’, ‘Chain’ and ‘Location’ on the fields pane:
That will create a nice table on the report canvas:
Sweet! 👍
Conclusion
Using calculated columns, you can add new columns to your data models. Instead of getting the values from a data source, you define the values in that column using DAX formulas.
At first, you might find this a bit complicated. However, once you start making one, you’ll see how easy it really is. And as it gets easier for you, try using more complicated DAX functions. 😊