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 Aggregation Functions in Power BI

Aggregation functions summarize multiple data values into a single result. In Power BI, DAX aggregation functions help calculate total sales, average revenue, minimum and maximum values, row counts, and other important business metrics.

These functions are commonly used in measures, cards, tables, charts, and dashboards. Choosing the correct aggregation is essential for producing accurate and meaningful reports.

Learning Objectives

  • Understand aggregation in DAX.
  • Use SUM, AVERAGE, MIN, and MAX.
  • Count rows and distinct values.
  • Understand the difference between COUNT, COUNTA, and COUNTROWS.
  • Use aggregation functions in measures and report visuals.
  • Recognize common aggregation errors.

1. What Is Aggregation?

Aggregation is the process of combining multiple values into a summary result. For example, a sales table may contain hundreds or thousands of transactions. An aggregation function can calculate their total sales amount or average transaction value.

Individual Values 5,000 3,000 2,000 Aggregation SUM = 10,000 AVERAGE = 3,333.33 MAX = 5,000 The function determines how the input values are summarized.
Diagram 1: Different aggregation functions summarize the same set of values in different ways.

2. Sample Sales Data

Use the following sample table to understand how the aggregation functions calculate results.

SaleID Product Quantity SalesAmount
1 Laptop 2 50,000
2 Mouse 3 1,500
3 Keyboard 2 2,500
4 Monitor 1 15,000

3. SUM Function

The SUM function adds all numeric values in a specified column, respecting the current filter context when used in a measure.

Syntax

SUM(TableName[ColumnName])

Example Measure

Total Sales = SUM(Sales[SalesAmount])

For the sample data, Total Sales returns 69,000 when all four records are included.

When to Use: Use SUM to calculate total revenue, total quantity, total cost, total profit, or other additive numeric values.

4. AVERAGE Function

The AVERAGE function calculates the arithmetic mean of numeric values in a column. It adds the numeric values and divides the result by the number of numeric values included in the calculation.

Average Sales = AVERAGE(Sales[SalesAmount])

For the sample data, the average sales amount is:

(50000 + 1500 + 2500 + 15000) / 4 = 17250
Important: Average transaction value is not always the same as average quantity or average product price. Select the column that matches the business question.

5. MIN and MAX Functions

MIN returns the smallest value in a column, while MAX returns the largest value. These functions are useful for identifying the lowest and highest sales amounts, prices, dates, or quantities.

Sales Amount Comparison Laptop 50,000 Mouse 1,500 Keyboard 2,500 Monitor 15,000 MIN = 1,500 MAX = 50,000
Diagram 2: MIN identifies the smallest sales value, while MAX identifies the largest.

Minimum Sales

Minimum Sales = MIN(Sales[SalesAmount])

Expected result: 1,500.

Maximum Sales

Maximum Sales = MAX(Sales[SalesAmount])

Expected result: 50,000.

6. COUNT, COUNTA, and COUNTROWS

DAX provides different counting functions. Choosing the right one depends on whether you need to count numeric values, nonblank values, or rows in a table.

Function Purpose Example
COUNT Counts values in a column according to DAX COUNT rules, primarily numeric values and supported date values. COUNT(Sales[Quantity])
COUNTA Counts nonblank values in a column, including text and numeric values. COUNTA(Sales[Product])
COUNTROWS Counts rows in a table or table expression. COUNTROWS(Sales)
DISTINCTCOUNT Counts distinct values in a column, including a blank value if present. DISTINCTCOUNT(Sales[Product])
COUNT Counts supported values in a column. COUNTA Counts nonblank values in a column. COUNTROWS Counts table rows in the current context. DISTINCTCOUNT Counts unique values in a column.
Diagram 3: Counting functions answer different questions about rows and column values.

Count Sales Rows

Sales Row Count = COUNTROWS(Sales)

Expected result for the sample data: 4.

Count Unique Products

Unique Products = DISTINCTCOUNT(Sales[Product])

Expected result for the sample data: 4.

Best Practice: If you want to count transactions, COUNTROWS is often a clear choice. If you want to count unique customers or products, use DISTINCTCOUNT on the appropriate key or column.

7. DISTINCTCOUNT Function

DISTINCTCOUNT counts unique values in a column. It is useful when the same customer, product, or order identifier appears in multiple rows.

Consider the following CustomerID values:

Transaction CustomerID
1 C01
2 C02
3 C01
4 C03
5 C02
Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

The expected result is 3 because the distinct customer IDs are C01, C02, and C03.

8. Use Aggregations in Report Visuals

After creating measures, you can use them in cards, charts, and tables. The same measure can produce different results when the report visual groups data differently.

Card Total Sales 69,000 Single summary value Bar Chart Laptop Mouse Keys Value by category Table Product Sales Laptop 50,000 Mouse 1,500 Keyboard 2,500 Values by row
Diagram 4: Measures can summarize the full dataset or calculate results for each visual grouping.

Recommended Visuals

  • Card: Display Total Sales or Average Sales.
  • Bar chart: Compare Total Sales by Product.
  • Table: Display product-level sales and quantity.
  • Slicer: Filter measures by date, product, or region.

9. Aggregation and Filter Context

Measures usually respond to the current filter context. For example, a Total Sales measure may show the full total in a card and separate totals for each product in a chart.

Total Sales Measure SUM(Sales[SalesAmount]) Laptop 50,000 Mouse 1,500 Keyboard 2,500 Each category applies a different filter context to the measure.
Diagram 5: The same measure returns category-specific results in a grouped visual.

10. Common Aggregation Mistakes

Mistake Why It Causes Problems Recommended Action
Using SUM on an identifier Adding IDs usually does not answer a meaningful business question. Use COUNTROWS or DISTINCTCOUNT when appropriate.
Using AVERAGE on the wrong column The result may not represent the intended business metric. Confirm whether the requirement is average price, quantity, or transaction value.
Confusing row count with unique count Repeated customer or product values can increase row counts. Use DISTINCTCOUNT for unique values.
Ignoring filter context Visual totals can change based on slicers and grouping. Test the measure in multiple visuals and filter conditions.
Incorrect data type Text values may not behave as expected in numeric aggregations. Validate column data types in Power Query or Model view.

11. Practical Exercise

Create aggregation measures using the following sample data.

SaleID Product Quantity SalesAmount
1 Laptop 2 50,000
2 Mouse 3 1,500
3 Keyboard 2 2,500
4 Monitor 1 15,000

Tasks

  1. Import the sample data into Power BI Desktop.
  2. Create a Total Sales measure using SUM.
  3. Create an Average Sales measure using AVERAGE.
  4. Create Minimum Sales and Maximum Sales measures.
  5. Create a Sales Row Count measure using COUNTROWS.
  6. Create a Unique Products measure using DISTINCTCOUNT.
  7. Display the results in Card visuals.
  8. Create a table visual showing Product and Total Sales.
  9. Add a Product slicer and observe how the measures change.

Expected Results

Measure Expected Result
Total Sales 69,000
Average Sales 17,250
Minimum Sales 1,500
Maximum Sales 50,000
Sales Row Count 4
Unique Products 4

12. Knowledge Check

Question 1

Which DAX function calculates the total of a numeric column?

Answer: SUM.

Question 2

Which function returns the smallest value in a column?

Answer: MIN.

Question 3

Which function counts rows in a table?

Answer: COUNTROWS.

Question 4

How do you count unique customer IDs?

Answer: Use DISTINCTCOUNT on the CustomerID column.

Question 5

Why can a measure return different values across visuals?

Answer: Different visuals can apply different filter contexts and groupings.

Summary

In this lesson, you learned how DAX aggregation functions summarize data and how to create measures using SUM, AVERAGE, MIN, MAX, COUNTROWS, COUNTA, and DISTINCTCOUNT.

You also practiced displaying aggregation results in cards, charts, and tables and explored how filter context affects measures. Always select the function that matches the business question, validate the underlying data types, and compare calculated results with the source data before using them in reports.