Aggregations for performance in Power BI - Dashboard Guide

Dashboard Mode - Aggregations for performance

Dashboard Goal

Understand how aggregations improve report speed by summarizing sales data by region and year.

Sample Data

OrderID	Region	Year	SalesAmount
1001	East	2022	150
1002	West	2022	200
1003	East	2023	300
1004	West	2023	250
1005	North	2022	100
1006	North	2023	180

Dashboard Components

• KPI Card: Total Sales
  Formula: Total Sales = SUM(Sales[SalesAmount])
  Result: 1180
• Aggregated Table: Sales by Region and Year
  Formula: SUMMARIZE(Sales, Sales[Region], Sales[Year], "TotalSales", SUM(Sales[SalesAmount]))
  Shows total sales per region per year:
  

  Region	Year	TotalSales
  East	2022	150
  East	2023	300
  West	2022	200
  West	2023	250
  North	2022	100
  North	2023	180

• Bar Chart: Sales by Region
  Uses aggregated data to show total sales per region:
  Measure: Total Sales by Region = CALCULATE(SUM(Sales[SalesAmount]), ALLEXCEPT(Sales, Sales[Region]))
  Visual: Bars for East (450), West (450), North (280)

Dashboard Layout

+----------------------+----------------------+
|      Total Sales      |   Sales by Region    |
|       (KPI Card)      |      (Bar Chart)     |
+----------------------+----------------------+
|           Aggregated Sales by Region and Year           |
|                      (Table)                            |
+---------------------------------------------------------+

Interactivity

A slicer for Year filters all components. Selecting a year updates the KPI card, bar chart, and aggregated table to show sales only for that year. This demonstrates how aggregations speed up filtering by summarizing data before visualization.

Self Check

If you add a filter for Year = 2023, which components update and what is the new total sales?

Answer: All components update. The KPI card shows 730 (300+250+180). The bar chart shows sales by region for 2023: East 300, West 250, North 180. The aggregated table shows only rows for 2023.

Key Result

Dashboard shows total and aggregated sales by region and year to demonstrate performance benefits of aggregations.