Power BI Add Calculated Column [With Various Examples]

This Power BI Tutorial explains how we can add the calculated column in Power BI with various examples. Also, we will cover the below-mentioned headings:

1. Power Bi add calculated column
2. How to add a calculated column in Power BI
3. Power Bi adds the calculated column to the table visual
4. Power Bi add calculated column if statement
5. Power Bi adds a calculated column to the matrix visual
6. Power Bi adds a calculated column to dataset
7. Power bi adds calculated column count
8. Power Bi adds a calculated column from another table
9. Power Bi query editor adds a calculated column
10. Power Bi add calculated column date
11. power bi add index column to the calculated table
12. power bi online adds a calculated column
13. power bi add calculated column group by
14. power bi add a calculated column to report
15. power bi add a calculated column using filter
16. power bi add a calculated column to direct query
17. power bi add a calculated column in the query

Power Bi add calculated column

• Power BI Calculated columns allow us to add new data to the already existing table data. We can create a calculated column in the Power BI report view or data view by using the new column feature.
• When we create a Calculated column in Power BI, it appears in the Fields list just like any other field in the data, but we can identify the calculated column by a special icon showing its values are the result of a formula.

Also, Read: Power BI Matrix Multiple Column

How to add a calculated column in Power BI

Let us see how we can create and add a calculated column in Power BI with an example.

In this example I have used the Product sample data mentioned below, you can download it from here.

• There are two ways that we can access the New column option, either we can choose the New column option under the Table tools in the ribbon.
• Else we can create your new calculated column in the Product table, right-click on the table or select the ellipsis … an icon in the Fields pane, and select the New column option as shown below.

• We can name the calculated columns whatever we want to display in the report visualization just like the other column fields.
• In this example, I am going to create a calculated column to find the Remaining Qty based on the Total Qty and Sold Qty.
• Enter the below-mentioned Dax formula to calculate the new column based on another column and click on the check icon.

Remaining Qty = Products[Total Qty]- Products[Sold Qty]

Where,

1. Remaining Qty = Calculated column name
2. Products = Table Name
3. Total Qty and Sold Qty = Column Names

• In the below screenshot we can see that the newly created calculated displays the Formula bar appears in the field pane.

This is how to create and add a calculated column in Power BI.

Check: How to remove rows in power query editor [With various examples]

Power Bi add calculated column to table visual

Let us see how we can add the calculated column to the table visual in Power BI.

In this example also I have used the Product sample data:

• Open the Power BI Desktop, load the data and select the Modelling tab, and click on the New Column option to create a calculated column.
• And then apply the below-mentioned formula in the formula box:

Remaining Qty = Products[Total Qty]- Products[Sold Qty]

Where,

• Remaining Qty = Calculated column name
• Products = Table Name
• Total Qty and Sold Qty = Column Names

• Now in the report view, select the table visual, and in the columns section drag and drop the newly created calculated columns in Power BI.
• In the below screenshot, you can see that the calculated column displays the value correctly in the Table visual Power BI.

This is how to add the calculated column and display the value in the table visual Power BI.

Read: Power BI filter between two dates [With 15+ Examples]

Power Bi add calculated column if statement

Let us see how we can add the calculated column using the if statement condition in Power BI,

Here In this example also I have used the Product sample data, we will create a calculated column that calculates and displays the result, if the Sold Amount is greater than 3250, returns good else bad.

• Load the data to the Power BI Desktop and select the Modelling tab, and click on the New Column option to create a calculated column.
• And then apply the below-mentioned formula in the formula box:

Status = IF(Products[Sold Amount]>=3250,"Good","Bad")

Where,

1. Status = Calculated column name
2. Products = Table Name
3. Sold Amount = Column Name

• In the below screenshot, you can see that the status column displays the result based on the condition applied.

This is how to add the calculated column using the if statement condition in Power BI.

Also, check: Power BI Sum Multiple columns

Power Bi add calculated column to matrix

Let us see how we can add the calculated column to the matrix visual in Power BI.

In this example also I have used the Product sample data:

• Open the Power BI Desktop, select the Modelling tab, and click on the New Column option to create a calculated column.
• And then apply the below-mentioned formula in the formula box:

Product Status = IF(Products[Sold Qty]>=20,"Good","Bad")

Where,

• Product Status = Calculated column name
• Products = Table Name
• Sold Amount = Column Name

• Now in the report view, select the matrix visual, and in the columns section drag and drop the newly created calculated columns in Power BI.
• In the below screenshot, you can see that the calculated column displays the value correctly based on the condition applied in the data set in Matrix visual Power BI.

This is how to add the calculated column and display the correct value in the matrix visual Power BI.

Check out: Power BI divides two columns [With Examples]

Power Bi add calculated column to dataset

Let us see how we can add the calculated column field to a dataset in the Power BI,

Here we will see an example of Power BI calculated column average, we will create a calculated column to calculate the average of the Sold Qty.

• Open the Power BI Desktop, select the Modelling tab, and click on the New Column option to create a calculated column.
• And then apply the below-mentioned formula in the formula box:

Average = AVERAGE(Products[Sold Qty])

Where,

1. Average = Calculated column name
2. Products = Table Name
3. Sold Qty = Column Name

• In the below screenshot, you can see that the Average calculated column has been added to the data set.

This is how to add the calculated column field to a dataset in Power BI.

Read: Stacked Bar Chart in Power BI [With 27 Real Examples]

Power bi adds calculated column count

Let us see how we can add the calculated column using the count function in Power Bi,

Here in this example, we will count the product count and display the result in calculated column.

• Open the Power BI Desktop, select the Modelling tab, and click on the New Column option to create a calculated column.
• And then apply the below-mentioned formula in the formula box:

No of Products = COUNT(Products[Product])

Where,

1. No of Products = Calculated column name
2. Products = Table Name
3. Product = Column Name

• In the below screenshot, you can see that the calculated count column has been added to the Power Bi data model.

This is how to add the calculated column using the count function in Power BI.

Check: Power BI Bookmarks [With 21 Examples]

Power Bi adds a calculated column from another table

Let us see how we can add a calculated column from another table in Power BI,

Here in this example, I have used the department’s table data and employees’ table data as mentioned below: Make sure one column should have the same data so that we can make the relationship between the two tables:

Table 1 : [ Department Table]

Table 2 :[ Employees Table]

• In the two tables, you can see that the Department Id column data is the same, and by default, it makes one to one relationship as mentioned below:

• Open the Power BI Desktop, and load the two tables of data. And select the table for which you want to calculate the column and click on the New Column option to create a calculated column.
• And then apply the below-mentioned formula in the formula box:

Department Name = LOOKUPVALUE(Departments[Department Name],Departments[Department ID],Employees[Department Id])

Where,

1. Department Name = Calculated column name
2. Departments, Employees = Table Name
3. Department Name, Department ID = Column Names

• In the below screenshot, you can see that the calculated column has been added from the Departments table to the Employees table.

This is how to add a calculated column from another table in the Power BI.

Check out: Power bi change color based on value [With 13 real examples]

Power Bi query editor adds a calculated column

Let us see how we can add a calculated column through the Power BI query editor in Power BI,

Here in this example, I have used the department’s table data and employees’ table data which I used for the above example.

Now we will see how we can add the department name column from the department table to the employee table.

To achieve this follow the below-mentioned steps:

• Open the Power BI desktop, and load the table data into it, In the ribbon, under the Home option click on the Transform data option as shown below:

• Once we are redirected to the power query editor, select the table for which you want to add a calculated column, and in the ribbon choose the Merge Queries option.
• In the Merge window, Choose the Common column data from both the tables. In this example I have selected the Department Id as shown below:
• Select Join kind as Full outer option and click on the ok button.

• Now select the column that you want to add, under the expand option. If we want to display the original column name as a prefix select the check box if not uncheck the box and click on the ok button.

• Click on the close and Apply option, so that changes will appear on the Power BI desktop.
• In the below screenshot, you can see the newly created calculated column in the employee’s table.

This is how to add a calculated column through the Power BI query editor in Power BI.

Read: Power BI sync slicers [With 15 useful examples]

Power Bi add calculated column date

Let us see how we can add the calculated column date in the Power BI,

Here in this example, I have used the employees’ table data to add the calculated date column in Power BI,

• Open the Power BI Desktop, select the Modelling tab, and click on the New Column option to create a calculated column.
• And then apply the below-mentioned formula in the formula box:

Joining Date = DATE(2022,7,12)

Where,

• Joining Date = Calculated column name
• DATE = Function
• In the below screenshot, you can see that the calculated date column has been added to the Employees table.

This is how to add the calculated column data in Power BI.

Also, check out: Power BI Report Export to PDF [With 20+ Examples]

Power Bi adds an index column to the calculated table

Let us how we can add the index column to a calculated table in Power BI,

In this example, I have used the Employees sample data mentioned below,

• Open the Power BI desktop, and load the table data into it, In the ribbon, under the Home option click on the Transform data option as shown below:

• Once we are redirected to the power query editor, select the table for which you want to add an index calculated column, and in the ribbon choose the Add column option.
• Expand the index column and choose the starting index, either we can select index 0, index 1, or custom index.
• Here I am going to select index 0 as displayed below:

• Click on the close and Apply option, so that changes will appear on the Power BI desktop.
• In the below screenshot, you can see the newly created index calculated column in the employee’s table.

This is how to add a calculated index column through the Power BI query editor in Power BI.

Read: Power BI Compares Two Columns in Different Tables

Power BI online add calculated column

• No, it is not possible to add a calculated column in the Power BI online or Power BI service
• It is not supported to create calculated columns or measures in the Power BI service.

Power Bi add calculated column percentage

Let us see how we can add the calculated column percentage field in the Power BI,

Here we will see an example of Power BI calculated column percentage, we will create a calculated column to calculate the percentage of the Sold Qty based on the accessory.

• Open the Power BI Desktop, select the Modelling tab, and click on the New Column option to create a calculated column to find the sum of the accessory.
• And then apply the below-mentioned formula in the formula box:

Total accessory = CALCULATE ( SUM ( Products[Accessory] ),ALLEXCEPT(Products, Products[Product]))

Where,

1. Total accessory = Calculated column name
2. Products = Table Name
3. Accessory and Product = Column Names

• Later, create another calculated column to find the percentage of the sold qty based on the accessory value.
• Click on the New Column option to create a calculated column and then apply the below-mentioned formula in the formula box:

Percentage column = DIVIDE ( Products[Sold Qty], Products[Total accessory], 0 ) 

Where,

1. Percentage column = Calculated column name
2. Products = Table Name
3. Sold Qty and Total accessory = Column Names

• Now in the ribbon under the column tools, choose the format from general to a percentage as shown below:

This is how to add the calculated column percentage field in the Power BI.

Power Bi add calculated column group by

Let us see how we can add a group by calculated column in Power BI,

In this example, I have used the Vehicles sample data mentioned below,

• Open the Power BI desktop, and load the table data into it, In the ribbon, under the Home option click on the Transform data option.
• Once we are redirected to the power query editor, select the table and choose the group by option presented in the ribbon as mentioned below:

• In the Group by the window, under the basic option choose the column that you want to group by and enter the new calculated column name.
• Choose the operation, that you want to perform, and in the column, section chooses the column that you want to calculate.
• Click on the ok button.

• In the below screenshot, you can see that the newly created calculated column displays the sum count of grouped values.
• Click on the close and Apply option, so that changes will be reflected on the Power BI desktop.

This is how to add a group by calculated column in Power BI.

Read: Power BI Create Table From Another Table

Power bi add a calculated column using filter

Let us see how to filter and display the value in the calculated column in Power BI,

• In this example, I have used the Vehicles sample data, Open the Power BI desktop, and load the table data into it.
• Click on the New Column option to create a calculated column and then apply the below-mentioned formula in the formula box:

Filtered Column = IF(Vehicles[Parent] = "Cars", Vehicles[Child], BLANK())

Where,

1. Filtered Column= Calculated column name
2. Vehicles = Table Name
3. Parent and Child= Column Names

• In the below screenshot, you can see that it filters and displays the child’s value based on the parent’s value. Here I have filtered the car values in the condition.

Filtered Column = IF(Vehicles[Parent] = "Mobiles", Vehicles[Child], BLANK())

Where,

1. Filtered Column= Calculated column name
2. Vehicles = Table Name
3. Parent and Child= Column Names

• In the below screenshot, you can see that it filters and displays the child’s value based on the parent’s value. Here I have filtered the Mobile values in the condition.

This is how to filter and display the value in the calculated column in Power BI.

Read: Power BI Slicer Multiple Columns

Power Bi adds a calculated column to report

Let us see how we can add a calculated column to the report view in Power BI,

In this example also I have used the Vehicles sample data:

• Open the Power BI Desktop, load the data and select the Modelling tab, and click on the New Column option to create a calculated column.
• And then apply the below-mentioned formula in the formula box:

Filtered Column = IF(Vehicles[Parent] = "Mobiles", Vehicles[Child], BLANK())

Where,

1. Filtered Column= Calculated column name
2. Vehicles = Table Name
3. Parent and Child= Column Names

• Now in the Power BI report view, select the table visual, and in the columns section drag and drop the newly created calculated columns in Power BI.
• In the below screenshot, you can see that the calculated column displays the value correctly based on the applied condition in the Table visual Power BI.

This is how to add the calculated column and display the value in the table visual Power BI report.

Also, you may like some more Power BI Tutorials:

• Power BI Group By [With 51 real examples]
• Power bi conditional column [With 17 Useful Examples]
• Power BI Date Slicer [With 41 real examples]
• Power BI date hierarchy [With 21 real examples]
• Power BI split column [With 13 real examples]
• Power Query Add Column Date [15 Examples]

In this Power BI Tutorial, we have learned how to add calculated columns with some examples and also covered the below-mentioned topics:

• Power Bi adds the calculated column to table visual
• Power Bi add calculated column if statement
• Power Bi adds a calculated column to matrix
• Power Bi adds a calculated column to dataset
• Power bi adds calculated column count
• Power Bi adds a calculated column from another table
• Power Bi query editor adds a calculated column
• Power Bi add calculated column date
• power bi add index column to the calculated table
• power bi online adds a calculated column
• power bi add calculated column group by
• power bi add a calculated column to report
• power bi add a calculated column using filter
• power bi add a calculated column to direct query
• power bi add a calculated column in the query