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.