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

DAX and Visualization Assessment

This assessment evaluates your ability to create DAX calculations, interpret filter context, select suitable visualizations, and build meaningful reports in Power BI. Complete the practical tasks and knowledge checks using a valid sales dataset.

Assessment Scenario

The reporting team at CIIT TRANING INSTITUTE needs a sales report that shows revenue, profit, order counts, monthly trends, and product performance. Your task is to create the required measures and visualizations and verify that the report responds correctly to user selections.

1. Assessment Objectives

  • Write DAX measures for business calculations.
  • Use aggregation and iterator functions appropriately.
  • Understand how filter context affects measure results.
  • Choose visualizations based on the question being answered.
  • Build an interactive report using slicers and visual interactions.
  • Validate calculations against source data.
  • Present findings clearly and accurately.

Assessment Workflow

Prepare Data Verify model Write DAX Create measures Build Visuals Present results Validate Test behavior

2. Dataset Requirements

Use a sales table named Sales with columns similar to the following. If your actual model uses different names, update the DAX expressions accordingly.

Column Example Data type
OrderIDORD1001Text
OrderDate2026-01-15Date
ProductLaptopText
RegionWestText
Quantity3Whole Number
UnitPrice25000Decimal Number
UnitCost18000Decimal Number
Preparation tip: Check that OrderDate is a Date column and that Quantity, UnitPrice, and UnitCost have appropriate numeric data types before writing DAX.

3. DAX Assessment: Aggregation Measures

Task 1: Calculate Total Quantity

Total Quantity = SUM(Sales[Quantity])

This measure adds the quantities from the rows included in the current filter context.

Task 2: Calculate Total Sales

Total Sales = SUMX( Sales, Sales[Quantity] * Sales[UnitPrice] )

SUMX evaluates the multiplication for each row and then adds the resulting values. This is useful when revenue is calculated from quantity multiplied by unit price.

Task 3: Calculate Total Cost

Total Cost = SUMX( Sales, Sales[Quantity] * Sales[UnitCost] )

Task 4: Calculate Total Profit

Total Profit = [Total Sales] - [Total Cost]

Task 5: Calculate Profit Margin

Profit Margin % = DIVIDE( [Total Profit], [Total Sales], 0 )

Format Profit Margin % as a percentage. DIVIDE handles a zero denominator using the alternate result specified in the third argument.

How the Measures Relate

Total Sales Quantity × Unit Price Total Cost Quantity × Unit Cost Total Profit = Sales − Cost Profit Margin = Profit ÷ Sales

4. DAX Assessment: Filter Context

A measure is recalculated based on the filters applied to the report. For example, a Total Sales card can show all sales when no region is selected and only West region sales when the West region is selected.

Task 6: Calculate Sales for the West Region

West Region Sales = CALCULATE( [Total Sales], Sales[Region] = "West" )

CALCULATE evaluates a measure in a modified filter context. This expression applies a filter for the West region.

Task 7: Calculate Distinct Orders

Total Orders = DISTINCTCOUNT(Sales[OrderID])

DISTINCTCOUNT counts unique order identifiers in the current filter context. This may differ from the number of rows when one order has multiple transaction lines.

Remember: A calculated column is evaluated row by row during data refresh, while a measure is evaluated when a report query runs. Choose the calculation type based on the requirement.

5. Visualization Assessment

Choose visuals that match the question. Avoid selecting a chart only because it looks attractive; consider what the user needs to compare, track, or investigate.

Business question Recommended visual Field or measure
What is the total revenue? Card Total Sales
How does revenue change over time? Line chart OrderDate, Total Sales
Which products generate the most revenue? Bar chart Product, Total Sales
How does each region perform? Column or bar chart Region, Total Sales or Total Profit
What are the detailed order values? Table OrderID, Product, Quantity, Total Sales
How can users explore a specific period? Slicer OrderDate or Year

Visual Selection Guide

KPI Card $125K Total Sales One key value Line Chart Change over time Bar Chart Compare categories

6. Build the Assessment Report

  1. Create cards for Total Sales, Total Profit, Total Orders, and Profit Margin %.
  2. Add a line chart showing Total Sales by month.
  3. Add a bar chart showing Total Sales by Product.
  4. Add a regional comparison chart using Total Profit or Total Sales.
  5. Add a table with order-level details.
  6. Add Year, Region, and Product slicers.
  7. Use consistent titles, number formats, spacing, and colors.
  8. Test the report with different filter selections.

Suggested Report Layout

SALES ANALYSIS Total Sales $125K Total Profit $32K Total Orders 1,240 Profit Margin 25.6% Monthly Sales Trend Sales by Product Detail Table and Report Filters Order ID Product Region Quantity Total Sales Year

The displayed numbers are examples for layout planning only. Use values calculated from your own dataset in the actual report.

7. Practical Assessment Tasks

Part A: DAX Calculations

  1. Create Total Quantity.
  2. Create Total Sales using SUMX.
  3. Create Total Cost using SUMX.
  4. Create Total Profit.
  5. Create Profit Margin % using DIVIDE.
  6. Create Total Orders using DISTINCTCOUNT.
  7. Create a measure for sales in a specified region using CALCULATE.

Part B: Visualizations

  1. Create four KPI cards.
  2. Create a monthly sales line chart.
  3. Create a product comparison bar chart.
  4. Create a regional performance chart.
  5. Create an order details table.
  6. Add Year, Region, and Product slicers.

Part C: Validation

  1. Compare Total Sales with a trusted source total.
  2. Check that Total Profit equals Total Sales minus Total Cost.
  3. Test each slicer individually and in combination.
  4. Write three findings supported by the report results.

8. Assessment Questions

Question 1: What is the difference between SUM and SUMX?

SUM adds the values in a single column. SUMX evaluates an expression for each row of a table and then sums the resulting values.

Question 2: Why is DIVIDE useful in a measure?

DIVIDE performs division and allows an alternate result to be returned when the denominator is zero or blank.

Question 3: What does CALCULATE do?

CALCULATE evaluates an expression in a modified filter context.

Question 4: Which visual is suitable for monthly sales trends?

A line chart is generally suitable for showing how sales change across dates or months.

Question 5: Why can Total Orders differ from Transaction Rows?

A single order may contain multiple transaction rows. DISTINCTCOUNT counts unique order identifiers, while COUNTROWS counts records.

Question 6: What should you do if a slicer changes a total unexpectedly?

Check the filter context, relationships, source data, and measure logic. Compare the filtered result with an independent calculation.

9. Evaluation Rubric

Assessment area Points Expected result
DAX measures 30 Measures calculate correctly and use suitable functions.
Visualization choices 20 Charts answer the intended business questions.
Report layout and formatting 15 Report is clear, consistent, and readable.
Interactivity 15 Slicers and visual interactions behave correctly.
Validation and accuracy 15 Calculations are checked against trusted results.
Documentation 5 Measures and findings are documented clearly.
Total 100 Complete and validated submission

10. Submission Checklist

  • Power BI report file (.pbix).
  • Screenshot of the final report page.
  • List of DAX measures created.
  • Brief explanation of visual selection.
  • Validation results for the main measures.
  • Three findings supported by the report.

Summary

In this assessment, you practiced writing DAX measures, applying filter context, selecting appropriate visuals, creating interactive reports, and validating calculations.

  • Use SUM for straightforward column aggregation.
  • Use SUMX when an expression must be evaluated row by row.
  • Use CALCULATE to evaluate an expression under modified filters.
  • Use DIVIDE for safer division.
  • Choose visuals that match the question being answered.
  • Test report interactions and compare results with trusted data.

A strong Power BI report combines accurate calculations, effective visual design, meaningful interactivity, and clear communication.