DATA4100 · Week 4 · Power BI Tutorial

Multi-Table Data
Modelling in Power BI

Four business questions answered by building a star schema: loading separate CSV tables, creating relationships, merging queries, and visualising connected data.

01Loading & transforming tables
02Creating relationships (model view)
03Merging queries (Price column)
04Calculated column: Total Sales
05Staff & customer performance visuals
06Product revenue ranking
Section 1 of 3
Building the
Data Model
Before we can build any visuals, we need to load our four CSV tables into Power BI, clean the headers, and establish the relationships that connect them into a single queryable model.
Tutorial Question 01
How do we load four separate CSV files into Power BI and connect them as a single data model?
Data Modelling Model View Star Schema
Why this matters: Real business data rarely arrives in one file. Sales data, customer profiles, product catalogues, and staff records are typically stored in separate systems. Power BI's data model lets us combine them without duplicating data — each table stays lean, and relationships do the joining work at query time.
Q01 Loading Tables & Creating Relationships ANSWER

Step-by-step in Power BI

1
Home tab → Get Data → Text/CSV. Navigate to customers.csv, click Load. Repeat for products.csv, staff.csv, orders.csv. Confirm all four appear in the Data pane (right side).
2
Home tab → Transform Data to open Power Query Editor. In the left panel, click customers. On the Home ribbon → Use First Row as Headers. Repeat for all four tables.
3
Click Close & Apply to load the cleaned tables back into Power BI.
4
Click the Model view icon (left sidebar — diagram icon). Power BI should have auto-detected three relationships based on matching column names. You will see lines connecting orders to each of the three lookup tables.
5
Click each relationship line to verify in the Properties pane. Confirm:
orders.CustomerID → customers.CustomerID
orders.ProductID → products.ProductID
orders.StaffID → staff.StaffID
Cardinality should be Many to one (*:1).
6
If any relationship is missing: drag the column name from the lookup table onto the matching column in the orders table to create it manually.
Tip: The orders table is the fact table — it records what happened. The other three are dimension (lookup) tables — they describe who, what, and who was involved.

Expected Model View

customers 🔑 CustomerID CustomerName Email · PhoneNumber products 🔑 ProductID ProductName Price staff 🔑 StaffID StaffName Email · HireDate orders (fact table) 🔑 InvID FK CustomerID FK ProductID FK StaffID Date · QTY 1 * 1 * 1 *
544
Rows in orders table
3
Relationships to verify
*:1
Cardinality (many orders to one customer)
Section 2 of 3
Enriching the Orders
Table
The orders table only records what was bought (ProductID) and how many (QTY) — not the price. We'll use Merge Queries to bring Price across from the products table, then create a Total Sales calculated column.
Tutorial Question 02
How do we bring the product price into the orders table and calculate total revenue per order line?
Merge Queries Calculated Column Power Query
Why this matters: The orders table records quantity but not price — price lives in the products table. To calculate revenue we need both. Merge Queries is Power BI's way of doing a database JOIN inside the query editor — adding columns from one table into another based on a shared key.
Q02 Merge Queries & Total Sales Column ANSWER

Step-by-step in Power BI

1
Home tab → Transform Data. In Power Query Editor, click orders in the Queries panel (left).
2
On the Home ribbon → click Merge Queries. A dialog opens.
3
In the Merge dialog: the top table is already orders. From the dropdown, select products as the second table. Click ProductID in the orders preview, then click ProductID in the products preview to link them. Set Join Kind to Left Outer. Click OK.
4
A new products column appears at the end of orders. Click the expand icon (⇔) in the column header. In the checklist, tick only Price. Untick "Use original column name as prefix". Click OK.
5
Hold Ctrl and click both the QTY column header and the Price column header to select both. Right-click → Transform → Product. Power Query creates a new column with QTY × Price for each row.
6
Double-click the new column header and rename it Total Sales. Click Close & Apply.
Check: In Report view, expand the orders table in the Data pane. You should now see Price, QTY, and Total Sales as columns.

Before & After — Orders Table

BEFORE merge InvID ProductID QTY INV 9438 PROD006 1 INV 9439 PROD013 2 INV 9440 PROD008 3 INV 9441 PROD010 1 No price. No revenue. → Merge AFTER merge InvID QTY Price TotalSales INV 9438 1 $799 $799 INV 9439 2 $129 $258 INV 9440 3 $149 $447 INV 9441 1 $249 $249 Total Sales = QTY × Price 8 columns · 544 rows
8
Columns after merge (was 6)
$359K
Total revenue across all 544 orders
Section 3 of 3
Building Visuals from
the Connected Model
With the data model complete, we can now answer business questions that span all four tables — staff performance, customer rankings, and product revenue — none of which would be possible from a single CSV.
Tutorial Question 03
Which staff member generated the highest total sales revenue, and how does their order volume compare?
Cross-table aggregation Table visual Bar chart
Why this matters: The orders table holds revenue data; the staff names live in a separate table. Without the relationship we built, Power BI could only show StaffID (e.g., "ST006") — not the staff member's name. This visual demonstrates the payoff of correct data modelling: human-readable, actionable performance reporting.
Q03 Staff Performance — Table & Bar Chart ANSWER

Visual 1 — Summary Table

1
In Report view, click the Table visual icon in the Visualisations pane.
2
From the Data pane, drag staff → StaffName into the Columns field well. Then drag orders → QTY and orders → Total Sales into Columns. Power BI automatically sums both.
3
Click the Total Sales column header in the visual to sort descending — highest revenue staff at the top.

Visual 2 — Horizontal Bar Chart

4
Click a blank area of the canvas. Select the Clustered Bar Chart visual.
5
Drag staff → StaffName to Y-axis. Drag orders → Total Sales to X-axis. The chart auto-sorts bars longest first.
6
In the Format pane, set bar colour to your theme red. Add data labels: Format → Data labels → On.
Insight to share: Patricia Wright leads with $28,802 — nearly twice the revenue of the lowest performer. Is this a territory difference, a product mix difference, or a performance gap?
Field wells — Bar Chart
Y-axisstaff[StaffName]
X-axisSum of orders[Total Sales]

Sum of Total Sales by Staff Member

Staff Name $0 $8K $16K $24K $30K P. Wright $28,802 L. Young $28,432 M. Thompson $25,917 S. Lewis $23,347 S. Davis $22,206 S. King $22,101 K. Walker $20,892 N. Adams $20,710 A. Harris $19,371 S. Collins $16,571 Top 10 staff by revenue (showing partial list)
$28,802
Patricia Wright — top performer
$359,513
Total revenue (all staff · 1,092 units)
Tutorial Question 04
Which product category generates the most revenue — and which products are the top earners within each category?
Cross-table aggregation Column Chart Sort descending
Why this matters: Product revenue data requires both tables — the product name comes from products, the revenue calculation comes from orders. This visual is only possible because of the relationship we established in Model View. It directly answers the question: "Where should we focus our inventory investment?"
Q04 Product Revenue — Clustered Column Chart ANSWER

Step-by-step in Power BI

1
Click a blank area of the report canvas. Select the Clustered Column Chart visual from the Visualisations pane.
2
From the Data pane, drag products → ProductName to the X-axis field well.
3
Drag orders → Total Sales to the Y-axis field well. Power BI sums all order revenue for each product.
4
To sort by revenue: click the ellipsis (…) on the visual → Sort axis → Sum of Total Sales → Sort descending.
5
In the Format pane: set the column colour to your theme red. Turn on Data labels to show revenue values above each bar.
6
Add a chart title: "Total Sales by Product". Resize the visual to show all product names clearly on the X-axis — they will abbreviate if too narrow.
Insight: Drone and TV generate the highest total revenue despite not necessarily being the most frequently purchased items — they have high unit prices. Compare this to USB Drive: high volume, very low revenue per unit.
Field wells — Column Chart
X-axisproducts[ProductName]
Y-axisSum of orders[Total Sales]

Sum of Total Sales by Product

$0 $20K $40K $60K $80K Drone $50K TV $48K Laptop Smart- Camera Tablet Gaming S.watch Monitor Headph. SSD Printer Router Speaker Keybd Mouse USB BT Adp
Drone
Highest revenue product ($999 unit price)
$24
Bluetooth Adapter — lowest revenue
20
Distinct products across all orders