How to group by and sum Power BI columns?

by

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

Grouping measures for easier access in Power BI models

Step 1: Locate your measures in the data model

Important: You can group measure objects only in the Model View. Hence, make sure to switch from the Report or Data View to the Model tab (using the controls at the left hand side of your Power BI user interface) before moving on.

  • Once in the Model View, go to the Data Panel at your right hand side.
  • Locate your table, in our case, its name is Hiring Campaign.
  • As you can noted, the Measures (marked with the calculator icons) are not ordered in some logical way.

Step 2: Group your Power BI Measures in a folder

To group your table measures proceed as following:

  • In the Data Panel, pick the Measures you would like to bring together. Hit your keyboard ctrl button to select multiple objects.
  • Note that once you select your measures, the Properties pane open up.
  • In the Display Folder, enter the name of your Measures grouping folder.
  • A folder is created and all measures placed into it.
  • Once the folder is created, you can drag and drop additional measures or calculated columns into it.