Master Power BI From Scratch

Learn Power BI fundamentals, data sources, data transformation, data modeling, DAX, visualizations, reports, and dashboards through structured practical lessons.

COURSE SYLLABUS
1. Power BI Introduction
Introduction to Power BI Power BI Desktop and Service Power BI Workflow Power BI Terminology Power BI Workspace Overview
2. Power BI Installation and Interface
Power BI Installation Power BI Desktop Interface Report, Data and Model Views Power BI File Management Power BI Settings and Options
3. Power BI Data Sources
Excel and CSV Data Sources Text and Folder Data Sources Database Data Sources Web and Online Data Sources Data Source Credentials and Refresh
4. Power BI Data Transformation
Power Query Introduction Data Types and Column Operations Merge and Append Queries Split, Group and Pivot Columns Power Query Applied Steps
5. Power BI Data Cleaning
Missing and Null Values Remove Duplicates and Errors Text and Date Cleaning Replace and Fill Values Data Quality Validation
6. Power BI Data Modeling
Data Modeling Fundamentals Create Table Relationships Cardinality and Filter Direction Star Schema and Dimension Tables Date Tables and Model Optimization
7. DAX
DAX Introduction and Syntax DAX Aggregation Functions DAX Logical and Conditional Functions DAX Filter and Iterator Functions DAX Date and Time Intelligence
8. Power BI Calculations
Calculated Columns Measures and Implicit Measures Row Context and Filter Context Quick Measures and KPI Time-Based Business Calculations
9. Power BI Visualizations
Basic Power BI Charts Pie, Donut and Treemap Charts Cards, Tables and Matrices Maps and Geographic Visuals Advanced and Custom Visuals
10. Power BI Filters and Slicers
Filter Types and Filter Pane Slicers and Sync Slicers Drill Down and Drill Through Cross-Filtering and Highlighting Bookmarks and Buttons
11. Power BI Reports and Dashboards
Report Page Layout and Design Multi-Page Reports Dashboard Creation and Tiles Executive and Business Reports Report Usability and Accessibility
12. Power BI Formatting and Interactivity
Themes and Color Formatting Conditional Formatting Tooltips and Report Page Tooltips Visual Interactions and Edit Interactions Mobile Layout and Responsive Reports
13. Power BI Publishing and Sharing
Publish Reports to Service Workspaces and Apps Scheduled Refresh and Gateway Sharing and Permissions Row-Level Security and Deployment
14. Power BI Projects and Assessment
Sales Dashboard Project Student Performance Project Data Cleaning and Modeling Assignment DAX and Visualization Assessment Final Power BI Project and Assessment

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.

Calculated Column Workflow Existing Columns Quantity, UnitPrice DAX Formula Quantity × UnitPrice New Column SalesAmount The formula produces a value for each row.

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.

  1. Open your Power BI Desktop report.
  2. Select the Sales table in the Data or Fields pane.
  3. Open the Table tools or Modeling ribbon.
  4. Select New column.
  5. Enter the DAX formula in the formula bar.
  6. Press Enter to create the column.
  7. 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.

Sales and Profit Calculation Sales Amount Quantity × Unit Price − Total Cost Quantity × Cost Profit Sales − Cost Use consistent units and verify the meaning of each source column.

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.

IF Condition SalesAmount ≥ 20000? Yes No High Sales 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.

Best Practice: Arrange threshold conditions from the highest value to the lowest when using SWITCH(TRUE(), ...). This helps prevent a higher category from being captured by an earlier, broader condition.

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.

Important: Date columns should have a valid Date or Date/Time data type. For advanced time intelligence calculations, use a properly configured date table rather than relying only on extracted month or year columns.

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])
Calculated Column vs. Measure Calculated Column One result per row Stored in the model Measure Result for current context Calculated when queried Choose based on whether the result is row-level or context-dependent.

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.