VLOOKUP-ing product names and regions into a 300,000-row sales sheet makes the file huge and slow. Power Pivot (the Excel Data Model) links tables instead, and DAX measures calculate on the fly.

1. Load tables into the Data Model

Format each list as a Table (Ctrl+T): Sales (Date, ProductID, RegionID, Qty, Amount), Products (ProductID, Product, Category), Regions (RegionID, Region). Then Insert → PivotTable → Add this data to the Data Model for each, or use Power Pivot → Add to Data Model.

2. Create relationships

Power Pivot → Manage → Diagram View. Drag Sales[ProductID] onto Products[ProductID], and Sales[RegionID] onto Regions[RegionID]. That’s it — no lookup columns.

3. Your first measures

In the Power Pivot window, click in the calculation area below the Sales table and type:

Total Sales := SUM ( Sales[Amount] )
Total Qty   := SUM ( Sales[Qty] )
Avg Price   := DIVIDE ( [Total Sales], [Total Qty] )

DIVIDE returns blank instead of an error when the quantity is zero.

4. CALCULATE — the heart of DAX

Retail Sales := CALCULATE ( [Total Sales], Products[Category] = "Retail" )
Share of Total :=
    DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALL ( Regions ) ) )

CALCULATE changes the filter before calculating. ALL(Regions) removes the region filter, so the denominator is always the grand total — giving each region’s share.

5. Year over year

Add a proper Calendar table (one row per date), relate it to Sales[Date] and mark it as a date table, then:

Sales LY := CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( Calendar[Date] ) )
YoY %    := DIVIDE ( [Total Sales] - [Sales LY], [Sales LY] )

Measures vs calculated columns