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|
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.
1,559 total views, 5 views today