Now do it in Power BI Desktop
Data model: Power BI Desktop exercise
Build the star schema yourself: load the dimension tables and connect them to the facts. You practised this in the browser; now do it in your own copy of Power BI Desktop (free from Microsoft), on the same data, and check your numbers below.
1. Get the data
In Power BI Desktop: Home → Get data → Text/CSV, pick each file, then Load. Check that numbers came in as numbers and dates as dates (Table view, column header icons).
In Model view, check or create these relationships (many side first):
- financials[Product] > Products[Product]
- financials[Country] > Countries[Country]
2. The tasks
1. Sales by region
With the relationship to Countries in place, show Sales by Region (a column that only exists in Countries).
2. Sales per manager
Show Sales by Manager from the Countries table.
3. Manufacturing cost
Using the Products table, calculate what the units sold cost to make: Units Sold × the product's Manufacturing Price.
3. Check your numbers
Try every task first. Then open each answer: the numbers below come from the same data, so yours should match exactly.
1. Sales by region
Total Sales = SUM(financials[Sales])| Region | Total Sales |
|---|---|
| Americas | 70,866,837.15 |
| Europe | 47,859,513.11 |
| Total | 118,726,350.26 |
2. Sales per manager
Total Sales = SUM(financials[Sales])| Manager | Total Sales |
|---|---|
| [email protected] | 47,859,513.11 |
| [email protected] | 70,866,837.15 |
| Total | 118,726,350.26 |
3. Manufacturing cost
Manufacturing Cost = SUMX(financials, financials[Units Sold] * RELATED(Products[Manufacturing Price]))Result: 106,662,513