SUM and AVERAGE functions in Power BI - Step-by-Step Guide

Introduction

SUM and AVERAGE functions help you add up numbers or find their average in your data. They make it easy to see totals or typical values without doing math yourself.

When you want to see total sales for a month in your report.

When you need to find the average rating from customer feedback.

When you want to add up all expenses in a category.

When you want to compare average order size across regions.

When you want to quickly summarize numeric data in a table or chart.

Steps

Step 1: Click

- Modeling tab

The ribbon shows options to create new measures and columns

Step 2: Click

- New measure button

A formula bar appears at the top for typing your DAX formula

Step 3: Type

- Formula bar

The formula bar shows your typed formula

💡 For SUM, type: Total Sales = SUM(Sales[Amount])

Step 4: Press

- Enter key

The new measure is created and appears in the Fields pane

Step 5: Drag

- New measure from Fields pane to a visual (like a card or table)

The visual updates to show the total or average value

Step 6: Repeat

- Steps 2 to 5

Create an average measure by typing: Average Sales = AVERAGE(Sales[Amount])

Before vs After

Before

No total or average values shown in the report visuals

After

Visuals show total sales as 125,000 and average sales as 5,208

Settings Reference

New measure

📍 Modeling tab > New measure

Create a calculation like SUM or AVERAGE to use in reports

Default: No measure

Fields pane

📍 Right side panel

Drag measures into visuals to display results

Default: Shows all data fields and created measures

Common Mistakes

Typing SUM without specifying a column, like SUM(Sales)

SUM requires a single column reference, not a table

Use SUM(Sales[Amount]) to sum the Amount column

Using AVERAGE on a text column

AVERAGE only works on numeric columns

Use AVERAGE on numeric columns like Sales[Amount]

Summary

SUM adds all numbers in a column to give a total.

AVERAGE calculates the mean value of numbers in a column.

Create these as measures in the Modeling tab to use in visuals.