Calculated fields in Google Sheets - Dashboard Guide

Dashboard Mode - Calculated fields

Dashboard Goal

See total sales, average sales per order, and profit margin for each product to understand sales performance.

Sample Data

Order ID	Product	Quantity	Unit Price	Cost per Unit
1001	Notebook	10	5	3
1002	Pen	20	2	1
1003	Notebook	5	5	3
1004	Pen	15	2	1
1005	Folder	7	4	2
1006	Folder	3	4	2

Dashboard Components

• Total Sales per Product (KPI card)
  Formula: =SUMPRODUCT((B2:B7=F2)*(C2:C7)*(D2:D7))
  Explanation: Calculates total sales for product in F2 by multiplying quantity and unit price for matching product rows.
• Average Sales per Order (KPI card)
  Formula: =AVERAGEIF(B2:B7,F2,C2:C7*D2:D7)
  Explanation: Finds average sales amount per order for product in F2.
• Profit Margin % (Calculated field)
  Formula: =((D2*C2)-(E2*C2))/(D2*C2) in row context
  Explanation: Calculates profit margin percentage per order by subtracting cost from sales and dividing by sales.
• Summary Table (Table showing Product, Total Sales, Average Sales, Profit Margin %)
  Uses formulas above for each product Notebook, Pen, Folder listed in F2:F4.

Dashboard Layout

+----------------------+----------------------+----------------------+
| Total Sales: Notebook | Average Sales: Notebook | Profit Margin: Notebook |
+----------------------+----------------------+----------------------+
| Total Sales: Pen      | Average Sales: Pen      | Profit Margin: Pen      |
+----------------------+----------------------+----------------------+
| Total Sales: Folder   | Average Sales: Folder   | Profit Margin: Folder   |
+----------------------+----------------------+----------------------+
|                          Summary Table                          |
+-----------------------------------------------------------------+

Interactivity

User can change the product name in cells F2, F3, F4 to see updated KPIs and summary table values for that product. This acts like a filter for product selection.

Self Check

If you change the product name in cell F2 from "Notebook" to "Pen", which components update their values?

• Total Sales KPI for F2
• Average Sales KPI for F2
• Profit Margin KPI for F2
• Summary Table row for that product

Key Result

Dashboard shows total sales, average sales per order, and profit margin for selected products using calculated fields.