How to group by and sum Power BI columns?

Here’s a question from Jason:

I have a dataset containing sales orders info. I need to group data from different columns to generate a small dashboard. I understand that i am able to aggregate, then calculate the sum and average values per group using the Transform tab or using code. Can you provide a simple example? Thanks!

Import your Power BI data

To answer the question above we will use an imaginary table containing some HR related information. Here’s a screenshot of an extract of the table contents:

In this tutorial we will learn to group the table contents in two key ways: using Power Query and using the DAX.

Group by multiple columns in Power BI

In Power BI use the Get Data function to import your data.
From the upper Ribbon, hit the Transform button to open the Power Query interface.
From the Queries panel at the left hand side pick your Query (in our case, it’s Hiring Campaign).
Still in Power Query, navigate to the Transform tab.
Then at the left hand side hit the Group by icon.
In the Group By dialog, hit the Advanced radio button.
Now define the grouping columns in our case, we will group the data by Expertise and Role; and display the interviews sum and average number of hired employees:

Hit OK.
Your data will be aggregated accordingly

In the Queries Panel, rename your query.
Back to your Home tab, hit Close and Apply to close Power Query and return to Power BI.
From here, use the Reports View to create your dashboard.

Aggregate data with DAX SUMMARIZE

You can use the DAX functions SUMMARIZE or GROUP BY to aggregate your data:

Import your dataset.
Move to the Data View.
In the Ribbon Calculation tab hit New Table.
Use SUMMARIZE to aggregate your data. Write the following DAX in your formula:

Hiring Summary (DAX) = 
    SUMMARIZE(
        'Hiring Campaign','Hiring Campaign'[Expertise],'Hiring Campaign'[Role],
        " # of Interviews",Sum('Hiring Campaign'[Interviews])
        ,"Avg # of Hired Employees",AVERAGE('Hiring Campaign'[Hired])
        )

Commit your changes.
The result will be an aggregated virtual table that has similar content (including the aggregated sum and average columns) than the one we created using Power Query in the previous step.

Note: As a rule of thumb – make sure to use the SUMMARIZE function to group by data in Power BI.

Power BI DAX GROUP BY and Average

In case that your Dataset is not too large, you can also use the DAX GROUP BY function to summarize your data:

In the Data View , hit the New Table button.
Input the following DAX statement into your formula row:

Hiring Summary GROUP BY (DAX) = 
    GROUPBY('Hiring Campaign','Hiring Campaign'[Expertise],'Hiring Campaign'[Role],
        " # of Interviews",SUMX( CURRENTGROUP(),'Hiring Campaign'[Interviews])
        ,"Avg # of Hired Employees",AVERAGEX(CURRENTGROUP(), 'Hiring Campaign'[Hired])
        )

Note that unlike the SUMMARIZE function, when using GROUPBY we iterate over the CURRENTGROUP() subset and use an iterator (such as SUMX or AVERAGEX) to make our calculations.

Group table columns by Week in Power BI

To summarize our Dataset by columns by time proceed as following:

In the Data View, in the Calculation section, hit the New Table button.
Add a Week Number value to your table using the ADDCOLUMNS function.
Then use the newly created column to summarize your data as shown below:

Hiring by week = 
 SUMMARIZE(
 ADDCOLUMNS( 'Hiring Campaign', "Week Number", WEEKNUM('Hiring Campaign'[Date])),
 [Week Number], "Number of Interviews", SUM('Hiring Campaign'[Interviews]), 
 "Average Number of Hires", ROUND(AVERAGE('Hiring Campaign'[Hired]),1)
 )

This will render the following table:

Aggregate Power BI table by month example

In a similar fashion we can group data by month:

SUMMARIZE(
 ADDCOLUMNS('Hiring Campaign', "Month Number", FORMAT('Hiring Campaign'[Date], "mmmm")),
 [Month Number], "Number of Interviews", SUM('Hiring Campaign'[Interviews]), 
 "Average Number of Hires", ROUND(AVERAGE ('Hiring Campaign'[Hired]),1)
 )

This will return the following table

Additional Reading: A more complete tutorial on grouping data by month can be found here.