SUM and AVERAGE functions in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate the total sales using SUM.

Power BI

TotalSales = [1](Sales[Amount])

Drag options to blanks, or click blank then click option'

AMIN

BAVERAGE

CCOUNT

DSUM

Attempts:

3 left

💡 Hint

Common Mistakes

Using AVERAGE instead of SUM will calculate the average, not total.

Using COUNT counts rows, not sums values.

The SUM function adds all values in the Sales[Amount] column to get total sales.

2fill in blank

medium

Complete the code to calculate the average sales amount.

Power BI

AverageSales = [1](Sales[Amount])

Drag options to blanks, or click blank then click option'

AMAX

BMIN

CAVERAGE

DSUM

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM will add values, not find the average.

Using MAX or MIN returns highest or lowest value, not average.

The AVERAGE function calculates the mean of all values in Sales[Amount].

3fill in blank

hard

Fix the error in the measure to correctly sum the Quantity column.

Power BI

TotalQuantity = SUM([1])

Drag options to blanks, or click blank then click option'

ASales[Quantity]

BQuantity[Sales]

CQuantity

DSales.Quantity

Attempts:

3 left

💡 Hint

Common Mistakes

Using only the column name without table causes errors.

Using dot notation like Sales.Quantity is not valid in DAX.

The correct syntax is SUM(Sales[Quantity]) where Sales is the table and Quantity is the column.

4fill in blank

hard

Fill both blanks to calculate the average sales amount for the year 2023.

Power BI

AverageSales2023 = CALCULATE([1](Sales[Amount]), Sales[Year] [2] 2023)

Drag options to blanks, or click blank then click option'

AAVERAGE

BSUM

C=

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM instead of AVERAGE will sum values, not average.

Using > instead of = will filter for years greater than 2023.

Use AVERAGE to calculate the average and = to filter for year 2023.

5fill in blank

hard

Fill all three blanks to create a measure that sums sales amount only for products with sales greater than 100.

Power BI

HighSales = CALCULATE([1](Sales[Amount]), Sales[Amount] [2] [3])

Drag options to blanks, or click blank then click option'

ASUM

B>

C100

DAVERAGE

Attempts:

3 left

💡 Hint

Common Mistakes

Using AVERAGE instead of SUM will calculate average, not total.

Using < instead of > will filter for sales less than 100.

Use SUM to add amounts, > to filter greater than, and 100 as the threshold.

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