DashCoreHub
Mission M14 · Power Pivot · 30 min
Build a data model: margin by store type
Request from your manager
The CFO wants to compare Supermarket and Express stores. Store type isn't in the sales file and the official unit cost lives in the products table, so instead of copying columns around you'll build a small data model with three related tables and a DAX measure for gross margin %.
Data
Download the practice file(s):
Steps
- Load sales_flat.csv, products.csv and stores.csv with Data > From Text/CSV > Load To… > Only Create Connection + 'Add this data to the Data Model'. Name the tables sales, products and stores.
- Data > Manage Data Model (Power Pivot) > Diagram View. Drag sales[product_name] onto products[product_name] and sales[store_name] onto stores[store_name] to create two one-to-many relationships.
- In the sales table add a calculated column: Year = YEAR(sales[order_date]).
- In the Calculation Area of sales create measures: Total Net Sales, Total Cost (using RELATED unit_cost) and Gross Margin % (format as percentage).
- Insert > PivotTable > From Data Model. Put stores[store_type] in Rows, sales[Year] in Filters (choose 2025), and the Gross Margin % measure in Values.
- Read the Supermarket value.
Check your answer
What was the 2025 gross margin % for Supermarket stores? (type the percent, e.g. 45.67)
Show solution
Supermarkets sold $75,467.11 in 2025 with $38,162.34 of cost, a gross margin of 49.43%. Express stores come in at about 49.60%, so the two formats earn almost the same margin per dollar even though Supermarkets sell more in total. RELATED pulls each line's unit_cost across the relationship, and DIVIDE avoids divide-by-zero errors.
Year := YEAR(sales[order_date]) -- calculated column
Total Net Sales := SUM(sales[net_sales])
Total Cost := SUMX(sales, sales[quantity] * RELATED(products[unit_cost]))
Gross Margin % := DIVIDE([Total Net Sales] - [Total Cost], [Total Net Sales])Answer: 49.43 %