Table references in formulas in Excel - Dashboard Guide

Dashboard Mode - Table references in formulas

Dashboard Goal

See total sales and average sales per product using Excel table references in formulas.

Sample Data

Product	Region	Sales
Apples	East	100
Bananas	West	150
Apples	West	200
Bananas	East	120
Cherries	East	90
Cherries	West	110

Dashboard Components

• KPI Card: Total Sales
  Formula: =SUM(SalesData[Sales])
  Shows total sales from the Sales column in the table.
• KPI Card: Average Sales per Product
  Formula: =AVERAGE(SalesData[Sales])
  Calculates average sales using the Sales column.
• Table: Sales by Product
  Formula in Sales Total column: =SUMIFS(SalesData[Sales], SalesData[Product], [@Product])
  Shows total sales for each product using structured references.

Dashboard Layout

+----------------------+-------------------------+
| Total Sales (KPI)    | Average Sales (KPI)     |
| 770                  | 128.33                  |
+----------------------+-------------------------+
| Sales by Product Table                      |
| +----------+------------+----------------+ |
| | Product  | Region     | Sales Total    | |
| +----------+------------+----------------+ |
| | Apples   | (from data)| 300            | |
| | Bananas  | (from data)| 270            | |
| | Cherries | (from data)| 200            | |
+--------------------------------------------+
    

Interactivity

Adding a filter for Region will update all components to show sales only for the selected region. The total sales, average sales, and sales by product will recalculate based on the filtered table data.

Self Check

If you add a filter to show only Region = East, which components update and what will the new total sales be?

Answer: All components update. Total sales become 310 (100 + 120 + 90).

Key Result

Dashboard shows total and average sales using Excel table references with dynamic filtering by region.