Open In App

Power BI – How to Create Calculated Columns?

Improve
Improve
Like Article
Like
Save
Share
Report

Power BI Desktop there are two data modification options known as Calculated Table and Calculated Column. These two options can add a whole new table referring to existing tables and a column to any existing table using existing columns of the respective table. Calculated Columns and Calculated Tables are generally made by writing a DAX query. 

Dataset

The database we’ll be using for implementation purposes is called Superstore dataset by Kaggle. This dataset contains two sheets Orders and Returns, in this implementation, we’ll use the Orders sheet. The sheet contains information about the customer name, address, id, product name, order id, product category, etc. The columns we are going to use are the Sale column and the Profit column. From these columns, we’ll generate a calculated column named the Cost column.

Steps to Create Calculated Columns

Step 1: Import data using the excel connector. 

For implementation purposes, we are using the superstore.xlsx file as the data source for Power BI Desktop.

Selecting-dataset-connector

 

Now you’ll need to select an excel file and one popup window will emerge as follows. In that window select which sheet for the excel file you want to work on and click the Load button.

Loading-data-sheet-to-Power-BI-Desktop

 

Step 2: Filtering the data. 

Data in Power BI is often unorganized, un-filtered, and messy, so to make accurate reports in Power BI you will need to organize, and filter the data in Power Query Editor. In Power Query Editor you need to perform some basic filtration like removing unwanted columns, removing black, and reassigning datatypes (if needed). To open the Power Query Editor, you must click on Transform data on the menu bar under the Home section.

Position: Home/Queries/Transform data

Filtering and rearranging-data-in-Power-Query-Editor

 

In the Power Query Editor, to remove unwanted columns choose the columns you want to remove and click on remove columns.

Position: Home/Manage Columns/Remove Columns

Selecting-and-removing-unnecessary-columns

 

After removing unwanted columns, now we need to remove blank rows. To remove blank rows click on the dropdown icon of remove rows and select the second last option remove blank rows.

Removing-blank-rows

 

The data was not distorted so we don’t have any other filtration on data, now to save the changes you made click on the Close & apply option in the top left corner.

Position: Home/Close/Close & Apply

Click-on-Close-Apply-to-save-changes

 

Step 3: Adding Calculated Column. 

To add a calculated column first you’ll have to select the table to which you will be adding the column from the Fields pane of the Power BI Desktop window. In this implementation, we are having only one table so it is obvious to choose it. After selecting the table there will be a new option group on the menu bar. 

Position: Table tools/New column

Select-table-and-click-on-New-column

 

In this implementation for the new column, we’ll calculate the cost per item, by the simple formula cost = sale – profit or cost = sale + loss. This formula will be written in DAX query language. After writing the query press enter to apply it to the data.

DAX-query-for-adding-Cost-column

 

  • DAX Query: Dax stands for Data Analysis Expression. DAX queries return results as a table right within the tool, allowing you to create and test the performance of your DAX formulas quickly. DAX queries are often used to create derived data columns or tables in Power BI Desktop. DAX query will affect the data in Power BI Desktop but not the data in Power Query Editor.

Now that changes have been applied, you’ll see a new column in the Field Pane of the window, named Cost.

Added-new-calculated-column

 

Output:

Values-of-cost-column

 


Last Updated : 05 Feb, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads