Excel data import in Power BI - Dashboard Guide

Dashboard Mode - Excel data import

Dashboard Goal

This dashboard answers: "How can we analyze sales data imported from Excel to understand total sales and sales by product?"

Sample Data Imported from Excel

Order ID	Product	Quantity	Unit Price	Sales Date
1001	Notebook	5	10	2024-01-10
1002	Pen	10	2	2024-01-11
1003	Notebook	3	10	2024-01-12
1004	Marker	7	3	2024-01-13
1005	Pen	15	2	2024-01-14
1006	Marker	2	3	2024-01-15

Dashboard Components

• Total Sales (KPI Card): Shows total sales amount.
  Formula: Total Sales = SUMX('Sales', 'Sales'[Quantity] * 'Sales'[Unit Price])
  Result: 5*10 + 10*2 + 3*10 + 7*3 + 15*2 + 2*3 = 50 + 20 + 30 + 21 + 30 + 6 = 157
• Sales by Product (Bar Chart): Displays total sales amount per product.
  Formula: Sales by Product = SUMX(FILTER('Sales', 'Sales'[Product] = EARLIER('Sales'[Product])), 'Sales'[Quantity] * 'Sales'[Unit Price])
  Results:
  • Notebook: (5+3)*10 = 80
  • Pen: (10+15)*2 = 50
  • Marker: (7+2)*3 = 27
• Sales Data Table: Shows raw imported data for reference.

Dashboard Layout

+----------------------+-----------------------+
|      Total Sales      |    Sales by Product    |
|       (KPI Card)      |       (Bar Chart)      |
+----------------------+-----------------------+
|                  Sales Data Table               |
+-------------------------------------------------+

Interactivity

A slicer on Product allows filtering all components by selected product(s). Selecting a product updates the Total Sales KPI and the Sales by Product chart to show only data for that product. The Sales Data Table also filters to show only relevant rows.

Self Check

If you add a filter to select only Pen in the Product slicer, which components update and what is the new Total Sales value?

Answer: The Total Sales KPI updates to 50 (10*2 + 15*2). The Sales by Product chart shows only Pen with sales 50. The Sales Data Table shows only rows with Product = Pen.

Key Result

Dashboard showing total sales, sales by product, and raw sales data imported from Excel with product filtering.