Power BI Online Training in Australia

Calculated columns are a part of our every Power BI training in Australia. They are used for data enrichment and hence none of Power BI training in Australia is complete without covering this concept to some extent. This blog post will introduce you to the concept of a calculated column in Power BI and help you create your first calculated column.

To get started, you will have to load the data set into Power BI desktop. For this blog, we will be using a modified version of Adventure Works 2020 data set. The original Adventure Works 2020 dataset developed by Microsoft for DAX learning is available at: https://docs.microsoft.com/en-us/power-bi/guidance/dax-sample-model

This data set has been slightly modified to meet the learning objectives of this blog. To get started:

1. Open a new Power BI desktop file.
2. Click on Home tab in the Ribbon.
3. Click on Excel in Data section.
4. Select the file.
5. Click on Open.
6. Select all the sheets.
7. Click on Load.
8. Click on Model Tab in the Left Ribbon.

Here’s a summary of the dataset.

Source: Microsoft | Docs – DAX Sample Model Structure

Please note that the Adventure Works data set has been slightly modified.

Calculated Column and its use case

Calculated columns and measures are created to add additional information in a data set. They add value to the dataset by bridging the gap between data model and good data visualization.

What is a Calculated Column:

Calculated Columns are a method for adding additional columns to the tables. Calculated Column is evaluated for each row of the table, creating a row context for each row. A naked column can be used while creating calculated columns. A Calculated Column is typically used in the axis, legend, filter or group area of a visualization.

Calculated Column is more intuitive for excel/business users. However, calculated column takes more disk space as its stored in data set. Therefore, creating a calculated column must be avoided if its use is un-certain.

To create a calculated column:

1. Right click on the Sales table in the Fields
2. Click on New Column.

Column tools menu opens, containing the formula bar to type the DAX code.

3. Type the following formula in the formula bar and press Enter.

In this Expression:

• Profit is calculated by subtracting the Total Product Cost from Sales Amount for each transaction.

4. Click on Profit (CC) in the Sales Table.

Column tools menu opens,

5. Click on the “$” and type 2 in the box.
6. Click on Sales column in the Sales Table.

Column tools menu opens,

7. Click on the “$” and type 2 in the box.
8. Click on the matrix visual in the visualization pane.
9. Drag and drop Category and Subcategory from the Products table to the rows.
10. Drag and drop Sales Amount and Profit (CC) from Sales to the Values.
11. Expand Accessories category and you will have the following visual

 Conclusion

Calculated columns are created in Power BI desktop using DAX. Each row of the column is assigned a value based on the expression being calculated. Calculated column bridges the gap between the raw data and information. This blog post presents a brief introduction to the concept of calculated columns and enlists the steps to create calculated columns in Power BI desktop.

Learning Power BI from Scratch? Get a complete, in-depth training on Microsoft Power BI by attending our training courses.

Power BI Training in Sydney

Power BI Training in Melbourne

Power BI Training in Brisbane