GROUP BY with aggregation in Google Sheets - Dashboard Guide

Dashboard Mode - GROUP BY with aggregation

Goal

Find total sales for each product category to see which category sells the most.

Sample Data

Order ID	Product	Category	Sales
1001	Notebook	Stationery	15
1002	Pen	Stationery	10
1003	Chair	Furniture	120
1004	Desk	Furniture	250
1005	Stapler	Stationery	8
1006	Lamp	Furniture	75
1007	Marker	Stationery	12

Dashboard Components

• Summary Table: Shows total sales per category.
  =QUERY(A1:D8, "select C, sum(D) group by C", 1)
  This groups sales by category and sums them.
• Top Category KPI: Shows the category with highest total sales.
  =INDEX(QUERY(A1:D8, "select C, sum(D) group by C order by sum(D) desc limit 1", 1), 2, 1)
  This finds the category with max sales.
• Total Sales KPI: Shows total sales across all categories.
  =SUM(D2:D8)

Dashboard Layout

+----------------------+------------------+
| Total Sales (KPI)    | Top Category (KPI)|
+----------------------+------------------+
|          Summary Table (Grouped Sales)       |
+----------------------------------------------+

Interactivity

Add a filter dropdown for Category. When you select a category, the Summary Table and KPIs update to show data only for that category.

Self Check

If you filter to show only 'Furniture' category, what is the total sales shown in the Total Sales KPI?

Answer: 445 (120 + 250 + 75)

Key Result

Dashboard shows total sales grouped by product category with key sales metrics.