Group By in Power BI: Simplify and Summarize Data

The Group By in Power BI lets you effectively Group and summarize an enormous number of associated data items. These include use cases such as sales analysis in regions, calculation of averages, identification of trends, and other functions that can turn a portion of huge datasets into actionable insights.

This blog elaborates on the Group By functionality in Power Query and DAX, covering both the basic and advanced approaches. This shall make it easier to get into analytics and create gripping reports.

What is Group By Function in Power BI?

With the Group By function, Power BI enables you to summarize and organize data by grouping rows according to a single or more than one column. Additionally, you can perform aggregate operations like sum, average, count, etc., for grouped data and even simplify analysis to provide concise summaries for reports and dashboards.

You now understand what Power Bi’s GroupBY Function is. Let’s examine how to use Power BI’s GroupBy Dax function.

How to Use GroupBy Dax Function in Power BI?

The SUMMARIZE function and the GROUPBY function are comparable. However, for any extension columns it adds, GROUPBY does not perform an implicit CALCULATE. In the extension columns it adds, GROUPBY allows the use of a new function, CURRENTGROUP, inside aggregation functions. Multiple aggregations can be carried out in a single table scan using GROUPBY. 

Syntax

GROUPBY (<table> [, <groupBy_columnName> [, <groupBy_columnName> [, …]]] [, <name>, <expression> [, <name>, <expression> [, …]]])

Parameters

• Table: Any DAX expression that returns a table of data.
• groupBy_columnName: The name of an existing column in the table (or in a related table,) by which the data is to be grouped. This parameter cannot be an expression
• name: The name given to a new column that is being added to the list of GroupBy columns, enclosed in double quotes.
• expression: One of the X aggregation functions with the first argument being CURRENTGROUP(). See With CURRENTGROUP section below for the full list of supported X aggregation functions.

Return value

A table with the selected columns for the groupBy_columnName arguments and the extension columns designated by the name arguments.

Remark

The following is what the GROUP BY function does:

• The designated table should be the first one (as well as all related tables in the “to-one” direction).
• Create a grouping by utilizing each GroupBy column (which must be present in the table from step #1).
• Each group represents a set of rows in the original table but appears as a single row in the result.
• Examine the extension columns being added for every group. In contrast to the SUMMARIZE function, the group is not put into the filter context, and no implied CALCULATE is carried out.
• It is required that groupBy_columnName be in a table or a related table.
• Double quote marks must be used around each name.
• The function uses the values of one or more groupBy_columnName columns to group a chosen set of rows into a set of summary rows. For every group, one row is returned.
• The main purpose of GROUPBY is to aggregate intermediate results from DAX table expressions. For effective aggregations over physical tables, the SUMMARIZE function or SUMMARIZECOLUMNS in the model is recommended.
• This function cannot be used in row-level security (RLS) rules or calculated columns in DirectQuery mode.

We will now comprehend the location of the GroupBY Button in the Power BI Query Editor.

Where to find the Group by button?

• The Group by button is located in three locations:

In the Transform group on the Home tab.

• On the Transform tab, in the Table group.

• On the shortcut menu, when you right-click to select columns.

Let’s examine how to use Power Query to group data in Power BI.

How to Group Data in Power BI with Power Query?

First, open the Power Query Editor to group data in Power BI. Next, select the Group By button after navigating to the Power Query Ribbon’s Transform section. Another option is to choose Group By with a right-click on a column header. This opens the Group By Dialog box to select columns to group and aggregation methods.

An example dataset of cookie sales by date will be used, which will be aggregated and grouped. Once the Power Query Editor is open, we go to the Power Query Ribbon’s Transform section and choose Group By.

After selecting a column to group the data by in the Group By dialog box, we must specify a new column for the aggregated values to be displayed, as well as an aggregation method and which column to apply it to.

Power Query does not permit us to use an existing column’s name because it handles all operations at the column level. However, once the data has been grouped, we can add a second step to rename the column.

Once grouped, the table of data will be reduced, showing only the unique categories that were grouped and the new aggregated values.

We’ll now look at how to group in Power Query by multiple columns.

How to Group By Multiple Columns in Power Query?

In Power Query, select Group By from the Transform section of the Power Query Ribbon to group by multiple columns. Toggle the Group By dialog box from Basic to Advanced when it displays. You can select multiple output columns with various aggregation techniques and add more columns to group by at the top when advanced mode is enabled.

The Add Grouping buttons show up after switching from basic to advanced mode, as shown in the dialog box below.

When grouping is added, Power BI will arrange data according to the hierarchy of the various columns that are assigned to the group, from top to bottom. The group at the top of the dialog box will be the column farthest to the left when you click OK and accept the grouping. Each column below will be to the right.

Grouping data using aggregation techniques and multiple columns is comparable to grouping data using a pivot table; each column you add becomes a part of the hierarchy, and the order in which they are grouped will affect the dataset you end up with.

Let’s look at how to group by one or more columns using an aggregate function.

Use an aggregate function to group by one or more columns

In this example, you want to sum up the total units sold by category and Country. Use the category and Sales Country to perform the group-by operation.

• On the Home tab, select the Group by option.
• Select Advanced to Group by multiple columns.
• Select the Category column.
• Select Add Grouping.
• Select the Country column.
• Enter Total units as the new column name, Sum as the operation, and Revenue as the column type.
• Select OK.

• This operation gives you the following table.

• This is how the Graph or visual will look like 

Perform an operation to GroupBy one or more columns

Use the following columns as group-by columns:

• Country 
• Category

Create two new columns using these steps:

• Apply the Sum operation to the Revenue column to aggregate it. Name this column Revenue.
• Use the All Rows operation to add a new Sub-Category column.

After that operation is completed, notice how the Sub-Category column contains [Table] values in each cell. Each [Table] value contains all of your original table’s rows grouped by the Country and Category columns.

Selecting the white space inside the cell displays a preview of the table’s contents at the bottom of the dialog.

The next step is to call the new column “will be” after extracting the row with the highest value in the Revenue column of the tables inside the new column. 

Retrieve the customized data

• You can create a new custom column with [table] values by choosing the Custom column from the General group under the Add Column tab on the ribbon.

• Give your new column the name Custom. Open the Table formula.Max([Total], “revenue”) under the formula for the Custom column.

A new column containing [Record] values is produced as a result of that formula. In essence, these record values are a single-row table. For every [Table] value in the Custome column, these records include the row with the highest revenue column value.

Let’s examine the benefits and drawbacks of grouping data in Power BI.

Pros and Cons of Grouping Data in Power BI

• Advantages of Grouping Data in Power BI:

Keen to facilitate data analysis: With grouping, it is now possible to summarize large data sets into appropriate categories, which helps identify insights within the data.

Improved Visual Clarity: Clustered data increases the ability of reports and dashboards to read much more clearly by cutting down clutter and adding aggregate values.

Custom Group: Using Power BI, the user can create custom groupings for non-numeric data, thus allowing flexibility in annotating the data.

• Disadvantages of Grouping Data in Power BI:

Granularity Loss: It may sometimes hide the necessary detailed information that is important for certain types of analysis.

Speed Degradation: Too much or complicated grouping, especially with large datasets, will affect report speed.

Static Grouping: Groups created manually in visuals are static, and may require updates if the underlying data changes. Dynamic grouping with DAX can address this but may add complexity.

Let’s look at how to use DAX Summarize to group data.

How to Group Data with DAX SUMMARIZE?

To produce a comparable Power Query Group result We can use the SUMMARIZE formula to accomplish this by function using DAX. The SUMMARIZE formula must be used after creating a new table in Power BI and specifying the table name, grouping column, resulting column name, and aggregation method.

This is the summarized syntax:

SUMMARIZE(

    <Table>,

    <GroupBy_ColumnName1>[, <GroupBy_ColumnName2>, ...],

    ["NewColumnName1", <Expression1>][, "NewColumnName2", <Expression2>, ...]

)

Implications of Using Power Query Vs. DAX

Depending on your particular requirements, you can choose between Power Query and DAX for Power BI data grouping. Power Query works best when pre-processing and structuring data before importing it into Power BI. It reduces the size of data and creates static groupings.

On the other hand, Power BI uses DAX for dynamic data analysis. It enables more intricate, real-time groupings in your reports according to filters and interactions. Each has advantages, and the most effective Power BI reports frequently combine the two to provide dynamic and effective data analysis. 

Conclusion

In Power BI, grouping data is simple and quick. There are several ways to carry out the operation, including DAX formulas like GROUPBY that offer greater flexibility than the SUMMARIZE function. It’s crucial to decide which approach is best for you, the volume of your data, and the number of developers who will need to handle and maintain your reports over time, as there are numerous ways to carry out a similar task.

Although Power Query’s group by function is arguably the most popular way to aggregate data, it restricts the amount of information that can be loaded into the reporting layer. Although DAX SUMMARIZE is dynamic and has no effect on the underlying data, it might not be feasible to use on big datasets.

Finally, we will view the frequently asked questions about GroupBY in PowerBI.

FAQs

1. How do you use group by function in Power BI?

The function Group By can be used in Power Query in Power BI to summarize data. To create a summary, select the column(s) for grouping, click Transform > Group By, and specify the aggregate function (e.g., sum, average). In DAX, one can use the functions GROUPBY or SUMMARIZE to group and aggregate data programmatically.

2. How does summarize differ from the group by power bi?

SUMMARIZE is a DAX function used to group data and create aggregated tables with more columns and calculated values.
GROUPBY is a more advanced DAX function for grouping, which uses CURRENTGROUP() for the row-level calculation and often requires complex logic beyond that.

3. What does Group By do in Power Query?

In Power Query, you can consolidate rows with values in selected columns and aggregate them. Summing and averaging are two types of aggregation. It is thereby possible to condense a table with large amounts of data into a smaller, summarized table, thus making it easier to analyze.

4. Grouping by custom column in Power BI?

To group by a custom column, create that column in Power Query (through Add Column > Custom Column) or in a DAX-calculated column first. Use the Group By option in Power Query or apply a DAX grouping function to aggregate based on that custom column.

This wraps up our blog post on Group By in Power BI: Simplify and Summarize Data. We’ve explored how this feature helps organize and aggregate data effectively, unlocking valuable insights for analysis and reporting.

If you’re looking to enhance your expertise in Power BI and master the Group By functionality, consider exploring advanced training programs and tutorials to advance your data analysis skills. We recommend you take up the Microsoft Power BI Certification Training: PwC Academy offered by Edureka. The Edureka’s Power BI certification course by PwC offers dual certification in business intelligence. The training is live instructor-led and provides hands-on experience in real-time projects. It prepares you for the official PL-300 exam and offers simulated real-world scenarios.

Do you have any questions or need further information? Feel free to leave a comment below, and we’ll respond as soon as possible!