Table of contents
In this blog post, we’ll dive into what calculated columns are, how to create them, and provide practical examples using a sales dataset.
What are Calculated Columns in Power BI?
Calculated columns are new columns you can create in your data model using Data Analysis Expressions (DAX). These columns are computed for each row in a table and stored as part of the dataset. Calculated columns are particularly useful for adding new data fields that don't exist in your source data, such as creating a full name by combining first and last names or calculating a profit margin based on sales and cost data.
How to Create a Calculated Column in Power BI
Creating a calculated column in Power BI is straightforward. Follow these steps:
Open Power BI Desktop: Start by launching Power BI Desktop and loading your dataset.
Select a Table: Navigate to the “Data” view and select the table where you want to add the calculated column.
Create the Calculated Column:
In the Fields pane, right-click on the table name and choose New Column.
A formula bar will appear where you can enter your DAX expression. For instance, to create a calculated column for the full name of customers, you might write:
Full Name = Customers[FirstName] & " " & Customers[FamilyName]
Name Your Column: The part of the DAX expression before the equals sign (
=) is the name of your calculated column. Choose a name that clearly describes the content of the column.Use the Column: Once created, the calculated column will appear in the table and can be used in your reports just like any other column.
Dataset Overview
Let’s briefly review the dataset (Download it from here) we’ll use for our examples. The dataset contains the following tables:
Customers: Contains customer information such as ID, name, gender, country, city, age, and marital status.
Products: Holds product details including title, category, unit price, weight, and manufacturer.
Stocks: Displays current stock levels for each product.
Orders: Captures order transactions including customer ID, product ID, quantity, unit price, and total amount.
Examples of Calculated Columns in Power BI
Example 1: Full Name
Let’s start by creating a calculated column that combines the first name and family name of each customer into a full name.
Full Name = Customers[FirstName] & " " & Customers[FamilyName]
This calculated column concatenates the FirstName and FamilyName columns with a space in between, providing a complete name for each customer.
Example 2: Profit Margin
Suppose we want to calculate the profit margin for each product in the Orders table. Assuming the cost is 70% of the unit price, the formula might look like this:
Profit Margin = Orders[UnitePrice] * 0.3
This formula creates a new column that represents the profit margin for each order by multiplying the unit price by 30%.
Example 3: Year of Order
If you need to extract the year from the order date, you can create a calculated column that does this:
Order Year = YEAR(Orders[DateAndTime])
This column will extract the year from the DateAndTime field, providing a clearer understanding of when orders were placed.
Example 4: Product Category & Title
To simplify reporting, you may want to create a single column that combines the product category and title:
Product Info = Products[Category] & " - " & Products[Title]
This calculated column merges the Category and Title fields from the Products table, making it easier to analyze data by category and product name together.
Example 5: Order Quantity Classification
You might want to classify orders based on the quantity ordered, creating categories like "Small", "Medium", and "Large":
Order Size =
IF(Orders[Quantity] <= 10, "Small",
IF(Orders[Quantity] <= 50, "Medium",
"Large"))
This DAX formula categorizes each order based on the Quantity column, creating a new field that labels each order as "Small," "Medium," or "Large".
Conclusion
Calculated columns are a powerful feature in Power BI that enable you to create new data fields tailored to your analysis needs.