Value fields and aggregation in Excel - Dashboard Guide

Dashboard Mode - Value fields and aggregation

Dashboard Goal

Understand total sales and average sales per product category to see which category performs best.

Sample Data

Order ID	Product Category	Sales Amount
1001	Electronics	250
1002	Furniture	450
1003	Electronics	300
1004	Clothing	150
1005	Furniture	200
1006	Clothing	350
1007	Electronics	400

Dashboard Components

• Total Sales: A KPI card showing total sales for all orders.
  Formula: =SUM(C2:C8)
  Result: 2100
• Average Sales per Category: A table showing average sales for each product category.
  Formula for Electronics average (example): =AVERAGEIF(B2:B8,"Electronics",C2:C8)
  Results:
  - Electronics: 316.67
  - Furniture: 325
  - Clothing: 250
• Sales by Category: A summary table showing total sales per category.
  Formula for Electronics total (example): =SUMIF(B2:B8,"Electronics",C2:C8)
  Results:
  - Electronics: 950
  - Furniture: 650
  - Clothing: 500

Dashboard Layout

+-------------------+--------------------------+
|   Total Sales     |  Average Sales per Cat.  |
|      2100         |  Electronics: 316.67     |
|                   |  Furniture: 325          |
|                   |  Clothing: 250           |
+-------------------+--------------------------+
|          Sales by Category Summary Table          |
|  Electronics: 950  Furniture: 650  Clothing: 500  |
+---------------------------------------------------+

Interactivity

Add a filter for Product Category. When you select a category, the Total Sales, Average Sales, and Sales by Category components update to show data only for that category.

Self Check

If you filter the dashboard to show only Furniture, what is the Total Sales value shown?

Answer: 650

Key Result

Dashboard shows total and average sales by product category using sum and average aggregation.