Loading to worksheet or data model in Excel - Dashboard Guide

Dashboard Mode - Loading to worksheet or data model

Goal

Understand how to load data either directly into an Excel worksheet or into the Excel Data Model for better analysis.

Sample Data

Order ID	Product	Category	Quantity	Price
1001	Notebook	Stationery	5	2.5
1002	Pen	Stationery	10	1.2
1003	Desk Lamp	Electronics	2	15
1004	Chair	Furniture	1	45
1005	Monitor	Electronics	3	120
1006	Stapler	Stationery	4	5

Dashboard Components

• KPI Card: Total Sales (Worksheet)
  Formula: =SUMPRODUCT(D2:D7,E2:E7)
  Result: 5*2.5 + 10*1.2 + 2*15 + 1*45 + 3*120 + 4*5 = 12.5 + 12 + 30 + 45 + 360 + 20 = 479.5
• KPI Card: Total Sales (Data Model)
  Power Pivot Measure:
  Total Sales = SUMX(Sales, Sales[Quantity] * Sales[Price])
  Result: 479.5
• Pivot Table (Worksheet Data): Shows total sales by Category using calculated column:
  Calculated Column formula:
  =[@Quantity]*[@Price]
  Pivot sums this column by Category:
  Stationery: (5*2.5)+(10*1.2)+(4*5)=12.5+12+20=44.5
  Electronics: (2*15)+(3*120)=30+360=390
  Furniture: 1*45=45
• Pivot Table (Data Model): Uses measure Total Sales grouped by Category from data model.
  Same results as worksheet pivot.

Dashboard Layout

+----------------------+----------------------+
|  Total Sales (WS)    |  Total Sales (DM)    |
|      479.5           |      479.5           |
+----------------------+----------------------+
|      Pivot Table: Sales by Category           |
|  +------------------------------+            |
|  | Category    | Total Sales    |            |
|  | Stationery  | 44.5          |            |
|  | Electronics | 390           |            |
|  | Furniture   | 45            |            |
|  +------------------------------+            |
+-----------------------------------------------+

Interactivity

Adding a slicer for Category filters both pivot tables simultaneously. Selecting a category updates the total sales KPIs and pivot tables to show only data for that category.

Example: Selecting Electronics shows total sales 390 in both KPIs and pivot tables.

Self Check

If you add a filter to show only Stationery category, which components update and what is the new total sales?

• Both KPI cards update to 44.5.
• Both pivot tables show only Stationery rows with total sales 44.5.

Key Result

Compare total sales loaded to worksheet vs data model with category breakdown and interactive filtering.