Calculated Columns

Microsoft Power BI allows you to create a calculated columns means you can add a new column to an existing table in your data model, and instead of querying and loading values for new column from data source their values are calculated using DAX formulas and other columns in table.

Lets demonstrate how to create a calculated columns in Power BI, here we have a dataset named ExecutiveSales as shown below.

First Name Last Name Qty_Sold Amount_Per_Qty
Rohit Sharma 13 3476
Ajay Kumar 46 7432
Atul Rawat 49 6324
Sujoy Ghosh 24 4672
Tarun Agarwal 89 5436
Mohit Sharma 13 1253
Diwakar Bhatt 98 4637
Himesh Lal 105 9574
Manoj Walia 45 65464

Lets create a simple summary report  of executive sales with this dataset as shown below.

You can see, the report displays executive’s first name, last name, quantity sold, and amount per quantity.

Creating a Calculated Columns

You can create a new calculated column either from fields pane, go to fields pane then right click on dataset and select New column from context menu or in ribbon bar click on Modeling tab then select new column.

Lets create a calculated column from fields pane, Go to field pane, right click on Dataset ExecutiveSales and select New column.

Once you click on New column, you will see a DAX formula bar opens, here you can write a DAX calculation for new column.

Lets create calculated column that display first name and last name together.

Following DAX, concatenates the values of first name and last name columns and returns the full name.

Full Name = ExecutiveSales[First Name] & " " & ExecutiveSales[Last Name]

Once you commit the DAX, you will see that calculated column as Full Name is created in Dataset ExecutiveSales, that you can see directly in fields pane.

[Also Read:  DAX Tutorial ]

Now to see the output of calculated column you can go either Data View Page, or simply drag the calculated column to table visual.

Lets drag the calculated column full name next to Last name field into table visual.

You can see, it returns the full name, which is the concatenation of first name and last name columns in table.

Lets see another way of seeing the calculated column output, Just go to Data view page as shown below.

Once you click on Data view page, you can see the output of calculated column as shown below.

Lets create another calculated column which will return the Total Sales Amount but this time we will create a calculated column with another option.

To creating a calculated column, click on Modeling Tab, then select New column.

Once you click on New Column, you will see it opens a DAX formula bar. That you have already been seen above.

Now you have to write a DAX for New calculated column, so to get the Total Sales done by each executive, you need to multiply Qty_Sold with Amount_Per_Qty as shown below.

Total Sales Amount = ExecutiveSales[Qty_Sold] * ExecutiveSales[Amount_Per_Qty]

Once you commit the DAX, It creates a new column Total Sales Amount in dataset.

Lets see the output of calculated column Total Sales Amount, just drag it into table visual.

You can see, it returns total sales done by each executives.

Also Read..

Group By the data using Power Query

Create Index column using Power Query

Merge Queries

Import text using examples feature Power BI

Pivot columns – turn rows into columns

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window

 261 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.