SUM and AVERAGE functions in Power BI - Deep Dive

Overview - SUM and AVERAGE functions

What is it?

SUM and AVERAGE are basic functions in Power BI used to add up numbers and find their average. SUM adds all the values in a column or expression. AVERAGE calculates the mean by adding values and dividing by the count. These functions help summarize data quickly.

Why it matters

Without SUM and AVERAGE, it would be hard to understand totals or typical values in data. They solve the problem of quickly summarizing large data sets to see overall trends or performance. Without them, reports would be confusing and less useful for decision-making.

Where it fits

Before learning these, you should know how to load data into Power BI and understand basic tables. After mastering SUM and AVERAGE, you can learn more complex calculations like filters, conditional sums, and advanced DAX functions.

Mental Model

Core Idea

SUM adds all numbers together, and AVERAGE finds the middle value by dividing the total by the count.

Think of it like...

Think of SUM as counting all apples in a basket, and AVERAGE as sharing those apples equally among friends to see how many each gets.

┌───────────────┐
│   Numbers     │
├───────────────┤
│  3  5  7  10  │
└───────────────┘
      │   │
      │   ├─> SUM = 3+5+7+10 = 25
      └────> AVERAGE = 25 / 4 = 6.25

Build-Up - 7 Steps

1

Foundation

Understanding SUM function basics

Concept: Learn how SUM adds all values in a column or expression.

In Power BI, SUM is used to add numbers in a column. For example, if you have a Sales column with values 10, 20, and 30, SUM(Sales) will give 60. You write it as a measure or calculated column using DAX: SUM(Table[Sales]).

Result

The total sum of the numbers in the selected column.

Understanding SUM is key because it is the foundation for many calculations like totals and aggregations in reports.

2

Foundation

Understanding AVERAGE function basics

Concept: Learn how AVERAGE calculates the mean of numbers in a column.

AVERAGE adds all numbers in a column and divides by how many numbers there are. For example, AVERAGE(Sales) for values 10, 20, 30 is (10+20+30)/3 = 20. In DAX, write AVERAGE(Table[Sales]).

Result

The average value of the numbers in the selected column.

Knowing AVERAGE helps you understand typical or central values in your data, which is important for comparisons.

3

Intermediate

Using SUM with filters

🤔Before reading on: do you think SUM automatically ignores filtered out rows or sums all data regardless? Commit to your answer.

Concept: SUM respects filters applied in reports or slicers, summing only visible data.

If you apply a filter to show only sales from 2023, SUM(Sales) will add only those filtered sales. This dynamic behavior lets you create interactive reports where totals update based on user choices.

Result

SUM changes based on filters, showing totals for the selected data subset.

Understanding that SUM respects filters is crucial for building interactive and accurate reports.

4

Intermediate

AVERAGE with missing or blank values

🤔Before reading on: do you think AVERAGE counts blank or missing values in its calculation? Commit to your answer.

Concept: AVERAGE ignores blank or missing values when calculating the mean.

If some rows have no sales value (blank), AVERAGE only divides by the count of rows with numbers. For example, values 10, blank, 30 result in (10+30)/2 = 20, not dividing by 3.

Result

AVERAGE reflects only actual numbers, avoiding distortion from blanks.

Knowing how AVERAGE handles blanks prevents wrong conclusions when data is incomplete.

5

Intermediate

Creating measures with SUM and AVERAGE

Concept: Learn to write reusable measures using SUM and AVERAGE for dynamic reporting.

Measures are formulas that calculate results on the fly. For example, create a measure Total Sales = SUM(Sales[Amount]) and Average Sales = AVERAGE(Sales[Amount]). These update automatically with filters and visuals.

Result

Dynamic totals and averages that respond to report interactions.

Using measures makes your reports flexible and powerful without changing the data.

6

Advanced

SUM and AVERAGE with calculated columns

🤔Before reading on: do you think SUM and AVERAGE can be used directly on calculated columns or only on raw data columns? Commit to your answer.

Concept: SUM and AVERAGE can aggregate calculated columns, which are columns created by formulas in the data model.

You can create a calculated column like Profit = Sales[Amount] - Sales[Cost], then use SUM(Profit) or AVERAGE(Profit) in measures. This lets you summarize derived data.

Result

Aggregated results based on custom calculations, not just raw data.

Knowing this expands your ability to analyze complex business metrics.

7

Expert

Performance considerations with SUM and AVERAGE

🤔Before reading on: do you think SUM and AVERAGE always perform well on large datasets, or can they slow down reports? Commit to your answer.

Concept: SUM and AVERAGE are fast but can slow down if used on complex expressions or very large data without optimization.

When used on simple columns, these functions are efficient. But if wrapped inside complex filters or calculated columns with many dependencies, performance can degrade. Using aggregations, indexing, or summarizing tables can help.

Result

Better report speed and user experience by optimizing calculations.

Understanding performance helps you design scalable reports that stay responsive.

Under the Hood

SUM and AVERAGE work by scanning the data column in memory and adding values. SUM adds all numbers directly. AVERAGE sums and counts non-blank values, then divides. Power BI's VertiPaq engine compresses data and uses columnar storage to speed these operations.

Why designed this way?

These functions were designed to be simple and fast for common aggregation needs. Columnar storage and compression allow quick scans. Ignoring blanks in AVERAGE avoids skewing results. Alternatives like manual loops would be slower and complex.

┌───────────────┐
│ Data Column   │
│ 10  20  30    │
└─────┬─────────┘
      │
  ┌───▼────┐       ┌───────────────┐
  │ SUM    │       │ AVERAGE       │
  │ Adds   │       │ Adds + Counts │
  │ values │       │ Divides       │
  └───┬────┘       └──────┬────────┘
      │                   │
  ┌───▼────┐         ┌────▼─────┐
  │ Result │         │ Result   │
  │ 60     │         │ 20       │
  └────────┘         └──────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does AVERAGE count blank values as zero in its calculation? Commit yes or no.

Common Belief: AVERAGE counts blanks as zero, lowering the average.

Reality: AVERAGE ignores blank or missing values and divides only by the count of actual numbers.

Why it matters: Believing blanks count as zero can lead to underestimating averages and wrong business decisions.

Quick: Does SUM always add all data regardless of report filters? Commit yes or no.

Common Belief: SUM adds all data in the column no matter what filters are applied.

Reality: SUM respects filters and slicers in Power BI, summing only visible data.

Why it matters: Ignoring filters causes confusion when totals don't match what users see in reports.

Quick: Can SUM and AVERAGE be used on text columns? Commit yes or no.

Common Belief: You can use SUM and AVERAGE on any column, including text.

Reality: SUM and AVERAGE only work on numeric columns; using them on text causes errors.

Why it matters: Trying to sum text causes errors and breaks reports, wasting time.

Quick: Does AVERAGE always divide by the total number of rows in the table? Commit yes or no.

Common Belief: AVERAGE divides by the total number of rows, including those with blanks.

Reality: AVERAGE divides only by the count of rows with numeric values, ignoring blanks.

Why it matters: Misunderstanding this leads to incorrect averages and misleading insights.

Expert Zone

1

SUM and AVERAGE behave differently when used inside CALCULATE with filters, affecting context transition.

2

Using SUMX or AVERAGEX allows row-by-row calculations before aggregation, enabling complex scenarios.

3

Performance can degrade if SUM or AVERAGE wrap complex expressions or large calculated columns without optimization.

When NOT to use

Avoid SUM and AVERAGE when you need weighted averages, distinct counts, or conditional aggregations. Use functions like SUMX, AVERAGEX, DISTINCTCOUNT, or CALCULATE with filters instead.

Production Patterns

In real reports, SUM and AVERAGE are often wrapped in measures combined with slicers and filters for dynamic dashboards. Experts use them with time intelligence functions to compare periods or calculate running totals.

Connections

Weighted Average

Builds-on

Understanding simple AVERAGE is essential before learning weighted averages, which adjust the mean based on importance or volume.

SQL Aggregation Functions

Same pattern

SUM and AVERAGE in Power BI correspond to SQL's SUM() and AVG(), so knowing SQL helps understand Power BI aggregations and vice versa.

Basic Arithmetic Mean in Statistics

Builds-on

AVERAGE in Power BI is the arithmetic mean, a fundamental statistical concept, linking BI tools to data science basics.

Common Pitfalls

#1 Trying to sum a text column causes errors.

Wrong approach:

TotalNames = SUM(Customers[Name])

Correct approach:

CountNames = COUNT(Customers[Name])

Root cause:

SUM only works on numeric data; text columns require counting, not summing.

#2 Using AVERAGE on a column with blanks and expecting blanks to count as zero.

Wrong approach:

AvgSales = AVERAGE(Sales[Amount]) // expecting blanks as zero

Correct approach:

AvgSales = AVERAGE(Sales[Amount]) // blanks ignored automatically

Root cause:

Misunderstanding how AVERAGE treats blanks leads to wrong expectations.

#3 Writing SUM without specifying the table or column correctly.

Wrong approach:

Total = SUM(Amount)

Correct approach:

Total = SUM(Sales[Amount])

Root cause:

DAX requires fully qualified column names to avoid ambiguity.

Key Takeaways

SUM adds all numeric values in a column, respecting filters and slicers in reports.

AVERAGE calculates the mean by dividing the sum of numbers by the count of non-blank values.

Both functions only work on numeric columns and ignore blanks in their calculations.

Using SUM and AVERAGE as measures creates dynamic, interactive reports that update with user selections.

Understanding their behavior with filters, blanks, and calculated columns is essential for accurate data analysis.