Calculated Columns in Power BI
Calculated columns allow you to create new columns in a data model by using DAX formulas. Each row receives a calculated value based on the formula and the data available in that row.
Calculated columns are useful for creating full names, profit values, conditional categories, date attributes, and other row-level calculations that can be used in tables, charts, slicers, and filters.
Learning Objectives
- Understand what calculated columns are.
- Create calculated columns using DAX formulas.
- Perform arithmetic calculations on each row.
- Use IF and SWITCH to create categories.
- Understand the difference between calculated columns and measures.
- Apply calculated columns in Power BI report visuals.
1. What Is a Calculated Column?
A calculated column is a new column created by writing a DAX formula. Power BI evaluates the formula for each row and stores the resulting values in the data model when the model is processed or refreshed.
For example, a Sales table may contain Quantity and UnitPrice columns. A calculated column can multiply these two values to produce a row-level SalesAmount.
2. Sample Sales Data
Use the following sample data to practise creating calculated columns. The table is named Sales.
| Product | Quantity | UnitPrice | CostPerUnit |
|---|---|---|---|
| Laptop | 2 | 50000 | 40000 |
| Mouse | 5 | 500 | 300 |
| Keyboard | 3 | 1200 | 800 |
| Monitor | 2 | 12000 | 9000 |
3. Create Your First Calculated Column
Follow these steps to create a calculated column in Power BI Desktop.
- Open your Power BI Desktop report.
- Select the Sales table in the Data or Fields pane.
- Open the Table tools or Modeling ribbon.
- Select New column.
- Enter the DAX formula in the formula bar.
- Press Enter to create the column.
- Verify the values in Data view.
4. Arithmetic Calculated Columns
Example 1: Calculate Sales Amount
Multiply the quantity sold by the unit price for each row.
SalesAmount =
Sales[Quantity] * Sales[UnitPrice]
| Product | Quantity | UnitPrice | SalesAmount |
|---|---|---|---|
| Laptop | 2 | 50000 | 100000 |
| Mouse | 5 | 500 | 2500 |
| Keyboard | 3 | 1200 | 3600 |
| Monitor | 2 | 12000 | 24000 |
Example 2: Calculate Profit
Profit is calculated by subtracting the row's total cost from its sales amount.
Profit =
Sales[SalesAmount] -
(Sales[Quantity] * Sales[CostPerUnit])
Example 3: Calculate Profit Margin
ProfitMargin =
DIVIDE(
Sales[Profit],
Sales[SalesAmount],
0
)
The ProfitMargin column returns a decimal value. Format the column as Percentage using the Column tools ribbon.
5. Text-Based Calculated Columns
DAX can combine text values to create useful labels and identifiers. For example, you can combine a product name with its category.
Example 1: Create a Product Label
ProductLabel =
Sales[Product] & " - " & Sales[Category]
This example assumes that the Sales table contains a Category column. For a table without Category, use Product or another available column.
Example 2: Create a Customer Full Name
FullName =
Customers[FirstName] & " " & Customers[LastName]
This example assumes a Customers table containing FirstName and LastName columns.
6. IF Function in Calculated Columns
IF checks a condition and returns one result when the condition is true and another result when it is false.
Example: Classify Sales Amount
SalesCategory =
IF(
Sales[SalesAmount] >= 20000,
"High Sales",
"Regular Sales"
)
Rows with a SalesAmount of 20000 or more receive the label High Sales. Other rows receive Regular Sales.
7. SWITCH Function for Multiple Categories
SWITCH is useful when you want to assign labels based on several possible values or conditions.
SalesBand =
SWITCH(
TRUE(),
Sales[SalesAmount] >= 50000, "Very High",
Sales[SalesAmount] >= 20000, "High",
Sales[SalesAmount] >= 5000, "Medium",
"Low"
)
The conditions are evaluated in order. When a condition is true, SWITCH returns its corresponding label. If no condition matches, the final result is Low.
8. Date-Based Calculated Columns
If your table contains an OrderDate column, you can extract date attributes to support grouping and reporting.
OrderYear =
YEAR(Sales[OrderDate])
OrderMonthNumber =
MONTH(Sales[OrderDate])
OrderMonthName =
FORMAT(Sales[OrderDate], "MMMM")
OrderQuarter =
"Q" & FORMAT(Sales[OrderDate], "Q")
To sort month names chronologically, select OrderMonthName and use Sort by column to choose OrderMonthNumber.
9. Calculated Columns vs. Measures
| Feature | Calculated Column | Measure |
|---|---|---|
| Calculation | Evaluated for each row. | Evaluated according to the current filter context. |
| Storage | Results are stored in the data model. | Results are calculated when required by a visual or query. |
| Typical use | Row-level categories, labels, and values. | Totals, averages, ratios, and dynamic KPIs. |
| Can be used in slicers | Yes, when appropriate. | Measures generally cannot be used directly as slicer fields. |
| Example | Sales[Quantity] * Sales[UnitPrice] |
SUM(Sales[SalesAmount]) |
10. Common Mistakes
| Mistake | Solution |
|---|---|
| Using a column name that does not exist. | Verify the table and column names in the model. |
| Multiplying the wrong fields. | Confirm which column contains quantity, unit price, cost, or total amount. |
| Getting an unexpected percentage. | Check the numerator, denominator, and percentage format. |
| Months appear alphabetically. | Sort the month name column by the month number column. |
| Using a calculated column for a dynamic total. | Consider a measure when the value should respond dynamically to filters. |
11. Student Practice Tasks
Task 1: Create a SalesAmount calculated column using Quantity and UnitPrice.
Task 2: Calculate Profit using SalesAmount and CostPerUnit.
Task 3: Create a ProfitMargin column using DIVIDE.
Task 4: Use IF to classify sales as High Sales or Regular Sales.
Task 5: Use SWITCH(TRUE()) to create four sales bands.
Task 6: Extract the year and month from OrderDate.
Task 7: Create a table visual that displays Product, SalesAmount, Profit, and ProfitMargin.
Task 8: Explain when a measure is more appropriate than a calculated column.
12. Knowledge Check
Question 1: What is a calculated column?
Answer: A model column whose values are calculated from a DAX formula for each row.
Question 2: Which function extracts the year from a date?
Answer: YEAR().
Question 3: Which function can classify values using multiple conditions?
Answer: SWITCH(TRUE(), ...) or nested IF functions.
Question 4: Why should month names be sorted by month number?
Answer: To display months in calendar order rather than alphabetical order.
Question 5: What is one major difference between a calculated column and a measure?
Answer: A calculated column produces a value for each row, while a measure calculates a result in the current filter context.
Summary
In this lesson, you learned how calculated columns create new row-level values using DAX formulas. You practised arithmetic calculations, text combinations, conditional categories, and date-based columns using functions such as IF, SWITCH, YEAR, MONTH, and FORMAT.
You also learned the difference between calculated columns and measures. Choose calculated columns for row-level values and categories, and use measures for calculations that need to respond dynamically to report filters. Always verify column references, data types, formula syntax, and calculated results.