SUM and AVERAGE functions in Power BI - Dashboard Guide

Dashboard Mode - SUM and AVERAGE functions

Business Question

How much total sales did we make, and what was the average sales amount per order last month?

Sample Data

Order ID	Sales Amount	Order Date
1001	150	2024-05-01
1002	200	2024-05-03
1003	350	2024-05-10
1004	400	2024-05-15
1005	300	2024-05-20
1006	100	2024-05-25
1007	250	2024-05-28

Dashboard Components

• KPI Card: Total Sales
  Formula: Total Sales = SUM('Sales'[Sales Amount])
  Result: 1750
• KPI Card: Average Sales per Order
  Formula: Average Sales = AVERAGE('Sales'[Sales Amount])
  Result: 250
• Bar Chart: Sales Amount by Order ID
  Shows each order's sales amount as a bar for easy comparison.
• Table: Detailed Sales Data
  Shows all orders with their sales amounts and dates.

Dashboard Layout

+----------------------+-----------------------+
|   Total Sales (KPI)  | Average Sales (KPI)   |
|        [1750]        |        [250]          |
+----------------------+-----------------------+
|                                              |
|          Bar Chart: Sales by Order ID         |
|                                              |
+----------------------------------------------+
|            Table: Detailed Sales Data         |
+----------------------------------------------+

Interactivity

A date slicer can be added to filter orders by date. When the user selects a date range, the Total Sales and Average Sales KPIs update to reflect only the orders in that range. The bar chart and table also update to show only filtered orders.

Self Check

If you add a filter to show only orders after 2024-05-15, which components update and what are their new values?

• Updated Total Sales: SUM of sales amounts for orders 1005, 1006, 1007 = 300 + 100 + 250 = 650
• Updated Average Sales: AVERAGE of those orders = 650 / 3 = 216.67
• Bar Chart and Table: Show only orders 1005, 1006, 1007

Key Result

Dashboard shows total and average sales with a bar chart and detailed table for last month's orders.

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