How to Create a Calculated Column in Power BI

Power BI is a data visualization tool on the Microsoft products library.

It is meant to fetch already created data and draw useful insights from it by running visualizations, analytics, and modeling.

However, Power BI was never meant to create or authorize data ✍

Does this mean you can make no changes to the data and add nothing to it once it’s loaded into Power BI?

Not entirely, you can still create calculated columns to add new data to your existing Power BI datasets to add more meaning to them.

This way you do not have to reach out for the data source app again and you can add more to your data table without having to alter your original data.

But how do you do that in Microsoft Power BI? 🤔

Let me walk you through the following guide to teach you how to create a calculated column in Power BI step-by-step.

Grab the workbook here so you can tag along, and let’s set the scene with an example below.

Table of Contents

What are Calculated Columns

Some of the times, the data you’re dealing with in Power BI might not contain a certain field that’s needed.

Power BI is excellent at analyzing and visualizing data but is not the best option to create new data 👩‍💻

However, for situations like these where you want small additions to your data from within your data, Power BI is not even that rigid to keep you from doing that.

This is where you add calculated columns in your Power BI data tables/reports 🧾

We use DAX formulas (Data Analysis Expressions) to calculate new columns. It can help you combine text values from multiple columns and calculate numerical values from other columns.

Creating a Calculated Column in Power BI

To create a calculated column to an existing data table in Power BI, it is crucial to have loaded the data table to Power BI already.

I have loaded some data tables from an Excel file on my Desktop 📃

To follow us along the way, you can download this sample spreadsheet here and practice the steps that follow next on your Power BI Desktop.

So, I have the data loaded and it looks like this 👇

The data consists of marks of students from different subjects (and some other tables).

I now want to add a new column to this dataset that shows the total marks of each student (aggregate of all subject marks).

To do that:

Step 1) Go to the Table View.

Step 2) In the Data pane on the left, select the table where you want a new calculated column added.

Step 3) From the Ribbon, go to Table Tools > Calculations group > New Column.

A new column will be added to the table 😎

To define the values for this table, jump to the formula bar.

Step 4) Name the new column as you like by writing it in the formula bar. I am writing it as Total Marks.

Step 5) Add an equal sign to begin defining the column values.

Here onwards it’s about telling Power BI to add the marks of all the subjects for each student.

So, for example, I want to add up Alen’s marks in English, Math, Science, and Arts 💼

All of these subjects are the columns of this table and Power BI recognizes table columns by the column header enclosed in square brackets.

Like [English], [Math], [Science] and [Arts].

Step 6) After the equal to sign, type an opening square bracket”[“ and start writing the column header for the column whose values you want to add. Like English.

Step 7) As soon as Power BI shows you the drop-down list of column names, go to the relevant column name and press the tab key. The column name will be inserted in the formula for you.

Step 8) As I want to add the marks of each subject, I will add a plus sign after it.

Step 9) Now define the next column’s name that you want added.

Step 10) Repeat the above steps until you have defined all the columns in the formula that you want added.

The syntax of the formula would become:

Step 11) Once the formula completes, press the Enter key.

There you have a new column added to your data table by the name “Total Marks’ that calculates the total marks of each student 👩‍🏫

Use your calculated Column in a report.

After you have created a calculated column in Power BI, know that it works in the same manner as other simple columns of your imported data table work.

You can use it in visualizations and create reports out of it 💻

Let me show you doing this with the “Total Marks” column that we just created.

Step 1) Go to the Report View.

Step 2) The table for “Students Marks” now includes a new column for “Total Marks”

Step 3) Select any visual from the Visualizations pane.

For example, I have selected a clustered column chart.

I have placed the Student Names on the X-axis and the Total Marks on the Y-axis to have it plotted as follows 📊

This is how the chart comes out like.

Just like this, you can create and manipulate as many visuals as you like using your newly created column.

How cool is that? 🆒

Conclusion

With this tutorial, we have learned how to create a calculated column in Power BI.

Creating calculated columns in Power BI is an excellent way to generate better insights from already existing data. The steps are simple, and some knowledge of DAX can help you perform a wide range of calculations 🤙

This saves time as you can add the necessary details to your data within the Power BI environment. In turn, this enables deeper and better data analysis and effective decision-making.

I hope you enjoyed learning how to create calculated columns in Power BI with us. I also invite you to check out the following interesting Power BI tutorials by Spreadsheeto.

• How to Create a Power BI Report (Step-by-Step & Examples)
• How to Use the Included Sample Data in Power BI (+Examples)