Aggregations for performance in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a business analyst at a retail company.

📋 Request: Your manager wants a report that shows total sales and average sales per customer by region and month. The dataset is large and slow to load. You need to improve report performance using aggregations.

📊 Data: You have a sales dataset with columns: Date, Region, CustomerID, SalesAmount.

🎯 Deliverable: Create a Power BI report with a table visual showing Region, Month, Total Sales, and Average Sales per Customer. Use aggregations to speed up the report.

Sample Data

Date	Region	CustomerID	SalesAmount
2024-01-05	North	C001	100
2024-01-15	North	C002	150
2024-01-20	South	C003	200
2024-02-10	North	C001	120
2024-02-15	South	C004	180
2024-02-20	South	C003	220
2024-03-05	North	C002	130
2024-03-10	South	C004	210
2024-03-15	North	C001	140
2024-03-20	South	C005	190

1

Step 1: Create a new aggregated table in Power BI that groups sales by Region and Month, summing SalesAmount and counting distinct CustomerID.

AggregatedSales = SUMMARIZE(SalesData, SalesData[Region], "Month", FORMAT(SalesData[Date], "YYYY-MM"), "TotalSales", SUM(SalesData[SalesAmount]), "DistinctCustomers", DISTINCTCOUNT(SalesData[CustomerID]))

Expected Result

A new table with columns: Region, Month (YYYY-MM), TotalSales, DistinctCustomers.

2

Step 2: Create a calculated measure for Average Sales per Customer using the aggregated table.

AverageSalesPerCustomer = DIVIDE(SUM(AggregatedSales[TotalSales]), SUM(AggregatedSales[DistinctCustomers]))

Expected Result

A measure that calculates average sales per customer for each Region and Month.

3

Step 3: Build a table visual in Power BI with rows as Region and Month, and values as TotalSales and AverageSalesPerCustomer.

Visual configuration: Rows = AggregatedSales[Region], AggregatedSales[Month]; Values = AggregatedSales[TotalSales], AverageSalesPerCustomer measure

Expected Result

Table shows total sales and average sales per customer by region and month, loading faster due to aggregation.

4

Step 4: Test report performance by comparing load time before and after using the aggregated table.

No formula; observe report refresh and interaction speed.

Expected Result

Report loads noticeably faster with aggregated data.

Final Result

Region   | Month   | Total Sales | Avg Sales/Customer
-----------------------------------------------
North    | 2024-01 | 250         | 125.00
South    | 2024-01 | 200         | 200.00
North    | 2024-02 | 120         | 120.00
South    | 2024-02 | 400         | 200.00
North    | 2024-03 | 270         | 135.00
South    | 2024-03 | 400         | 200.00

✓North region has steady sales with average sales per customer around 125-135.

✓South region shows higher average sales per customer at 200 consistently.

✓Using aggregated data improves report loading speed significantly.

Bonus Challenge

Create a dynamic aggregation that updates automatically when new sales data is added, maintaining performance.

Show Hint

Use Power BI's Incremental Refresh feature and parameterize the aggregation table to refresh only recent data.