Data model best practices in Power BI - Dashboard Guide

Dashboard Mode - Data model best practices

Goal

Understand how to build a clean and efficient data model in Power BI to create fast and accurate reports.

Sample Data

OrderID	CustomerID	ProductID	OrderDate	Quantity	Price	Region
1001	C001	P001	2024-01-10	2	50	North
1002	C002	P002	2024-01-15	1	30	South
1003	C001	P003	2024-02-05	5	20	North
1004	C003	P001	2024-02-20	3	50	East
1005	C004	P002	2024-03-01	4	30	West
1006	C002	P003	2024-03-15	2	20	South

CustomerID	CustomerName	Segment
C001	Alice	Retail
C002	Bob	Wholesale
C003	Charlie	Retail
C004	Diana	Wholesale

ProductID	ProductName	Category
P001	Widget	Gadgets
P002	Gizmo	Gadgets
P003	Thingamajig	Tools

Dashboard Components

• KPI Card: Total Sales
  Formula: Total Sales = SUMX(Sales, Sales[Quantity] * Sales[Price])
  Shows total revenue from all sales.
• KPI Card: Total Orders
  Formula: Total Orders = DISTINCTCOUNT(Sales[OrderID])
  Shows total number of orders.
• Bar Chart: Sales by Region
  X-axis: Region
  Y-axis: Total Sales (using Total Sales measure)
  Shows sales distribution across regions.
• Table: Sales Details
  Columns: OrderID, CustomerName, ProductName, Quantity, Price, Total Sale (calculated as Quantity * Price)
  Shows detailed sales data combining related tables.
• Relationships
  Sales[CustomerID] linked to Customer[CustomerID]
  Sales[ProductID] linked to Product[ProductID]
  These relationships enable combining data from multiple tables efficiently.

Dashboard Layout

+----------------------+----------------------+
|      Total Sales      |     Total Orders     |
|      (KPI Card)       |      (KPI Card)      |
+----------------------+----------------------+
|                      Sales by Region Chart                      |
|                          (Bar Chart)                           |
+---------------------------------------------------------------+
|                         Sales Details Table                    |
+---------------------------------------------------------------+

Interactivity

Filters on Region and Category allow users to narrow down data. Selecting a region updates the bar chart, KPI cards, and sales details table to show only data for that region. Selecting a product category filters the sales details and charts accordingly. Relationships ensure filters flow correctly between tables.

Self Check

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

• Total Sales and Total Orders KPI cards show totals only for South region.
• Sales by Region bar chart highlights or filters to South region data.
• Sales Details Table shows only orders from South region.

Key Result

A Power BI dashboard demonstrating best practices in data modeling with clean relationships, KPIs, charts, and detailed tables.