Sheets with Google Data Studio (Looker) in Google Sheets - Dashboard Guide

Dashboard Mode - Sheets with Google Data Studio (Looker)

Dashboard Goal

See how sales perform by product and region to find the best sellers and regions with highest revenue.

Sample Data

Order ID	Product	Region	Sales	Quantity	Order Date
1001	Notebook	East	120	3	2024-01-15
1002	Pen	West	80	10	2024-01-17
1003	Notebook	West	150	5	2024-01-20
1004	Marker	East	90	4	2024-01-22
1005	Pen	East	60	7	2024-01-25
1006	Marker	West	110	6	2024-01-28
1007	Notebook	East	130	4	2024-01-30

Dashboard Components

• Total Sales (KPI Card): Shows total sales amount.
  Formula in Sheets: =SUM(D2:D8)
  Result: 740
• Total Quantity Sold (KPI Card): Shows total quantity of items sold.
  Formula in Sheets: =SUM(E2:E8)
  Result: 39
• Sales by Product (Pivot Table): Groups sales by product.
  Formula in Sheets: Use Pivot Table with Rows=Product, Values=SUM of Sales.
  Result:
  Notebook: 400
  Pen: 140
  Marker: 200
• Sales by Region (Pivot Table): Groups sales by region.
  Formula in Sheets: Use Pivot Table with Rows=Region, Values=SUM of Sales.
  Result:
  East: 400
  West: 340
• Sales Trend by Date (Line Chart): Shows sales over time.
  Data: Use Order Date and Sales columns.
  Result: Sales increase and vary by date.

Dashboard Layout

+----------------------+----------------------+
| Total Sales (KPI)    | Total Quantity (KPI) |
+----------------------+----------------------+
| Sales by Product (Pivot Table)           |
|                                          |
+------------------------------------------+
| Sales by Region (Pivot Table)             |
|                                          |
+------------------------------------------+
| Sales Trend by Date (Line Chart)          |
|                                          |
+------------------------------------------+

Interactivity

Use a filter control in Google Data Studio connected to the Region and Product fields. When you select a region or product:

• All KPI cards update to show totals for the selected filter.
• Pivot tables refresh to show sales grouped by product or region within the filter.
• The sales trend line chart updates to show sales over time for the filtered data.

This lets you explore sales performance by different regions and products easily.

Self Check

If you add a filter for Region = East, which components update and what changes?

• Total Sales KPI: Updates to sum sales only for East region (120 + 90 + 60 + 130 = 400).
• Total Quantity KPI: Updates to sum quantity for East region (3 + 4 + 7 + 4 = 18).
• Sales by Product Pivot Table: Shows sales only for East region products:
  Notebook: 250
  Pen: 60
  Marker: 90
• Sales by Region Pivot Table: Shows only East region with total 400.
• Sales Trend Line Chart: Shows sales over time only for East region orders.

Key Result

Dashboard shows total sales, quantity, and sales breakdown by product and region with a sales trend line chart.