SUM and AVERAGE functions in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate total sales for each product category

You have a sales table with columns ProductCategory and SalesAmount. Which DAX measure correctly calculates the total sales for each product category?

A Total Sales = SUM(Sales[SalesAmount])

B Total Sales = AVERAGE(Sales[SalesAmount])

C Total Sales = SUM(Sales[ProductCategory])

D Total Sales = SUMX(Sales, Sales[ProductCategory])

💡 Hint

Think about which function adds numbers and which averages them.

The SUM function adds all values in the SalesAmount column, giving total sales. AVERAGE calculates the mean, which is not total. Summing ProductCategory is invalid because it is text. SUMX with ProductCategory also fails because it expects numbers.

❓ visualization

intermediate

Best visualization for average sales per region

You want to show the average sales amount per region in a report. Which visualization type is best to clearly communicate this?

A Pie chart showing count of sales transactions per region

B Clustered column chart showing average sales per region

C Stacked bar chart showing total sales per region

D Line chart showing average sales per region over time

💡 Hint

Choose a chart that compares average values across categories clearly.

A clustered column chart is good for comparing average sales across regions side by side. Stacked bar and pie charts are better for totals or proportions, not averages. Line charts show trends over time, but here time is not involved.

❓ data_modeling

advanced

Create a measure to calculate average sales per customer

You have a Sales table with SalesAmount and a Customers table with unique CustomerID. Which DAX measure correctly calculates the average sales per customer?

A Avg Sales per Customer = SUMX(Customers, AVERAGE(Sales[SalesAmount]))

B Avg Sales per Customer = AVERAGE(Sales[SalesAmount]) / DISTINCTCOUNT(Customers[CustomerID])

C Avg Sales per Customer = SUM(Sales[SalesAmount]) / DISTINCTCOUNT(Sales[CustomerID])

D Avg Sales per Customer = DIVIDE(SUM(Sales[SalesAmount]), DISTINCTCOUNT(Sales[CustomerID]))

💡 Hint

Think about dividing total sales by number of unique customers.

Option D correctly sums all sales and divides by the distinct count of customers in the Sales table. Option D incorrectly divides average sales by customer count, which is mathematically wrong. Option D uses DISTINCTCOUNT on Customers table which may not relate correctly. Option D tries to average inside SUMX incorrectly.

🔧 Formula Fix

advanced

Identify error in average sales measure

Consider this DAX measure to calculate average sales:

Average Sales = AVERAGE(Sales[SalesAmount]) + SUM(Sales[Quantity])

What is the main issue with this measure?

Power BI

Average Sales = AVERAGE(Sales[SalesAmount]) + SUM(Sales[Quantity])

A SUM cannot be used with Quantity column

B Syntax error due to missing parentheses

C It mixes average and sum, causing misleading results

D AVERAGE function requires two arguments

💡 Hint

Check if adding average and sum makes sense mathematically.

The measure adds average sales amount to total quantity sold, mixing different units. This leads to a meaningless number. There is no syntax error. SUM works on Quantity. AVERAGE takes one argument.

🧠 Conceptual

expert

Understanding filter context impact on SUM and AVERAGE

In Power BI, you have a measure Total Sales = SUM(Sales[SalesAmount]) and a measure Average Sales = AVERAGE(Sales[SalesAmount]). When you add a slicer filtering only one product, what happens to these measures?

A Both measures recalculate only for the selected product, showing filtered total and average

B Average Sales stays the same, Total Sales recalculates for selected product

C Total Sales stays the same, Average Sales recalculates for selected product

D Neither measure changes because slicers do not affect measures

💡 Hint

Think about how slicers change the data visible to measures.

Slicers apply filter context to visuals and measures. Both SUM and AVERAGE recalculate using only the filtered data for the selected product. So both show values for that product only.

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