SUM and AVERAGE functions in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to understand the total sales and average sales per transaction for each product category in the last quarter.

📊 Data: You have a sales dataset with columns: TransactionID, ProductCategory, SalesAmount, and TransactionDate.

🎯 Deliverable: Create a Power BI report showing total sales and average sales per transaction by product category for the last quarter.

Sample Data

TransactionID	ProductCategory	SalesAmount	TransactionDate
1001	Electronics	250	2024-01-15
1002	Clothing	80	2024-02-10
1003	Electronics	300	2024-03-05
1004	Home	150	2024-01-20
1005	Clothing	120	2024-03-15
1006	Home	200	2024-02-25
1007	Electronics	400	2024-03-28
1008	Clothing	60	2024-01-30

1

Step 1: Filter the data to include only transactions from the last quarter (January to March 2024).

Use Power BI filter on TransactionDate column: TransactionDate >= DATE(2024,1,1) && TransactionDate <= DATE(2024,3,31)

Expected Result

Filtered data includes all 8 rows as all dates are within Q1 2024.

2

Step 2: Create a measure to calculate total sales by product category.

Total Sales = SUM('Sales'[SalesAmount])

Expected Result

Total Sales sums sales amounts for each product category.

3

Step 3: Create a measure to calculate average sales per transaction by product category.

Average Sales = AVERAGE('Sales'[SalesAmount])

Expected Result

Average Sales calculates the average sales amount per transaction for each product category.

4

Step 4: Build a table visual in Power BI with ProductCategory as rows, and Total Sales and Average Sales as values.

Table visual configuration: Rows = ProductCategory, Values = Total Sales, Average Sales

Expected Result

Table shows total and average sales for Electronics, Clothing, and Home categories.

Final Result

ProductCategory | Total Sales | Average Sales
-------------------------------------------
Electronics     | 950         | 316.67
Clothing        | 260         | 86.67
Home            | 350         | 175.00

✓Electronics has the highest total sales of 950 with an average sale of about 317 per transaction.

✓Clothing has total sales of 260 with an average sale of about 87 per transaction.

✓Home category has total sales of 350 with an average sale of 175 per transaction.

Bonus Challenge

Create a line chart showing total sales trend by month for each product category in the last quarter.

Show Hint

Use the TransactionDate column to group sales by month and ProductCategory for the legend.

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