How to aggregate data in Power BI

Aggregating data means that you summarize data and present it at a higher level. For example, summarizing sales data and grouping it by date, product, or customer. The benefit of aggregating your data is that it reduces the table sizes in the data model, improves query performance, and allows users to focus on the important aspects of the data. 

Reasons for using aggregations in your data models:

• When working with a large amount of data (big data), aggregations will provide increased query performance, facilitate analysis, and insights from this large data. Aggregated data uses a fraction of the resources compared to more detailed data.
• If experiencing a slow refresh, aggregations will help to speed up the refresh process. The smaller cache size reduces the refresh time and enhances the user experience. Instead of refreshing millions of rows, you can reduce the number of rows significantly by using data aggregation.
• When you are working with a large data model, aggregations can be helpful to reduce and maintain the size of your model. Also if you anticipate that the amount of data will grow in the future. It can be smart to use aggregations proactively in order to future-proof the solution. 

Before creating aggregations, you should decide on the level at which you want to analyze the data. In this example, you want to aggregate the sales data on a weekly basis. When you have decided on the level, the next step is to choose how you want to create the aggregations. It is possible to create aggregations in different ways and each method will yield the same results.

• Directly in the database, you could create a table with the aggregation and thereafter import that table into Power BI Desktop.
• Directly in the database, you could create a view for the aggregation and then import that view into Power BI Desktop.
• In Power BI Desktop by using Power Query to create the aggregations step-by-step.

In the example below, we will use Power Query to perform the weekly aggregation. Notice that before the aggregation our table has more than 999 rows. 

We want to aggregate the data by the week_nb column and view the actual_sales_pcs column. Start by selecting Choose Columns on the Home tab. On the window that displays, select the columns that you want in the aggregation and then select OK.

Select the Group By option on the Home tab. On the window that displays, first select the column that you want to group by (week_nb), enter the name for the new column (Weekly Sales), choose Operation (Sum), and which column to summarize (actual_sales_pcs).

The data is now transformed and aggregated for each week, and you will be able to see the summarized sales quantity. Remember, that we started with over 999 rows. By aggregating the data we have significantly reduced the number of rows in the dataset from over 999 to 51 rows. This means that Power BI has less data to refresh and therefore should perform better.