SUM and AVERAGE functions in Power BI - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SUM function do in Power BI?

The SUM function adds up all the numbers in a column or expression and returns the total.

beginner

How does the AVERAGE function work in Power BI?

The AVERAGE function calculates the mean value by adding all numbers in a column and dividing by the count of those numbers.

beginner

Write a simple DAX formula to calculate the total sales using the SUM function.

Total Sales = SUM(Sales[Amount])
This adds all values in the Amount column of the Sales table.

beginner

Write a DAX formula to find the average sales amount.

Average Sales = AVERAGE(Sales[Amount])
This calculates the average of the Amount column in the Sales table.

beginner

Can SUM and AVERAGE functions be used on text columns?

No, both SUM and AVERAGE only work on numeric columns. Text columns will cause errors.

What will SUM(Sales[Amount]) return?

A The total of all sales amounts

B The average sales amount

C The count of sales records

D The maximum sales amount

The SUM function adds all values in the column, so it returns the total sales amount.

Which function calculates the mean value of a numeric column?

A AVERAGE

B COUNT

C SUM

D MAX

AVERAGE calculates the mean by dividing the sum by the count of values.

What happens if you use SUM on a text column?

A It returns the total length of text

B It counts the text entries

C It returns an error

D It converts text to numbers automatically

SUM only works on numbers. Using it on text causes an error.

Which DAX formula calculates the average sales amount?

A MAX(Sales[Amount])

B SUM(Sales[Amount])

C COUNT(Sales[Amount])

D AVERAGE(Sales[Amount])

AVERAGE(Sales[Amount]) returns the mean sales amount.

What is the result of SUM(Sales[Amount]) if the column has values 10, 20, and 30?

A 20

B 60

C 10

D 30

Adding 10 + 20 + 30 equals 60, so the sum is 60.

Explain in your own words how the SUM function works in Power BI.

Think about adding up all the values in a list.

Describe a situation where you would use the AVERAGE function in a sales report.

Consider when you want to know the usual sales amount per transaction.

Practice

1.

What does the SUM function do in Power BI?

easy

A. Adds all numbers in a column

B. Finds the highest number in a column

C. Counts the number of rows

D. Calculates the average of numbers

Solution

1. Step 1: Identify the purpose of SUM

   The SUM function adds all numeric values in a specified column, unlike AVERAGE (mean), COUNT (rows), or MAX (highest).

2. Final Answer:

   Adds all numbers in a column -> Option A

3. Quick Check:

   SUM = total addition [OK]

Hint: SUM always adds numbers, not counts or averages [OK]

Common Mistakes:

• Confusing SUM with AVERAGE
• Thinking SUM counts rows
• Mixing SUM with MAX or MIN

2.

Which of the following is the correct syntax to calculate the average of the Sales[Amount] column in DAX?

?

easy

A. SUM(Sales[Amount]) / COUNT(Sales[Amount])

B. AVERAGE(Sales[Amount])

C. AVG(Sales[Amount])

D. MEAN(Sales[Amount])

Solution

1. Step 1: Recall correct DAX average syntax

   In DAX, use AVERAGE(Sales[Amount]); AVG() and MEAN() do not exist, while SUM/COUNT works but is indirect.

2. Final Answer:

   AVERAGE(Sales[Amount]) -> Option B

3. Quick Check:

   AVERAGE() is the direct DAX function [OK]

Hint: Use AVERAGE() directly for mean in DAX [OK]

Common Mistakes:

• Using AVG() which is not a DAX function
• Trying MEAN() which doesn't exist in DAX
• Dividing SUM by COUNT manually instead of AVERAGE

3.

Given the table Sales with Amount values: 100, 200, 300, what is the result of this DAX measure?

TotalAmount = SUM(Sales[Amount])
AverageAmount = AVERAGE(Sales[Amount])

What are the values of TotalAmount and AverageAmount?

medium

A. TotalAmount = 600, AverageAmount = 200

B. TotalAmount = 300, AverageAmount = 100

C. TotalAmount = 600, AverageAmount = 300

D. TotalAmount = 200, AverageAmount = 600

Solution

1. Step 1: Compute SUM and AVERAGE values

   SUM(100 + 200 + 300) = 600; AVERAGE = 600 / 3 = 200.

2. Final Answer:

   TotalAmount = 600, AverageAmount = 200 -> Option A

3. Quick Check:

   SUM = 600, AVERAGE = 200 [OK]

Hint: Sum all values; average is sum divided by count [OK]

Common Mistakes:

• Mixing sum and average values
• Dividing sum by wrong count
• Confusing average with max or min

4.

Identify the error in this DAX formula intended to calculate the average sales amount:

AverageSales = SUM(Sales[Amount]) / COUNT(Sales)

medium

A. SUM should be replaced with AVERAGE

B. SUM cannot be divided by COUNT

C. No error, formula is correct

D. COUNT(Sales) should be COUNT(Sales[Amount])

Solution

1. Step 1: Locate COUNT syntax error

   COUNT(Sales) is invalid as COUNT requires a column like Sales[Amount], not a table.

2. Final Answer:

   COUNT(Sales) should be COUNT(Sales[Amount]) -> Option D

3. Quick Check:

   COUNT needs a column, not a table [OK]

Hint: COUNT needs a column, not a whole table [OK]

Common Mistakes:

• Using COUNT with a table instead of a column
• Assuming SUM/COUNT division is invalid
• Replacing SUM with AVERAGE without reason

5.

You have a Sales table with columns Region and Amount. You want to create a measure that shows the average sales amount only for the "East" region. Which DAX formula correctly calculates this?

?

hard

A. AverageEastSales = AVERAGE(Sales[Amount]) WHERE Sales[Region] = "East"

B. AverageEastSales = AVERAGE(FILTER(Sales, Sales[Region] = "East"))

C. AverageEastSales = CALCULATE(AVERAGE(Sales[Amount]), Sales[Region] = "East")

D. AverageEastSales = SUM(Sales[Amount]) / COUNT(Sales[Region] = "East")

Solution

1. Step 1: Construct correct filtered average DAX

   Use CALCULATE(AVERAGE(Sales[Amount]), Sales[Region] = "East") to apply filter context; others invalid (no WHERE, AVERAGE can't take table from FILTER directly, COUNT misused).

2. Final Answer:

   AverageEastSales = CALCULATE(AVERAGE(Sales[Amount]), Sales[Region] = "East") -> Option C

3. Quick Check:

   CALCULATE + filter = correct average [OK]

Hint: Use CALCULATE with filter for conditional averages [OK]

Common Mistakes:

• Using WHERE clause which is invalid in DAX
• Trying to filter inside AVERAGE directly
• Dividing SUM by COUNT with filter incorrectly