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.