There are multiple ways to group and bin with Power BI. We’ll explain them all and highlight different use cases when to use each one. Grouping and categorizing items into bins for better visualizations is the just the beginning of how to use this essential Power BI skill.
The easiest way to group items together in Power BI is by using the built-in grouping function for reporting, but users can also groups data to summarize columns in the Power Query Editor or even using DAX measures.
Let’s take a look!
Table of Contents
What is Grouping and Binning in Power BI
Grouping is a method in Power BI to combine values into larger buckets. When working in the report view, the setup is manual, where the user will define which values belong to a specific group. Once the group is established, it can be assigned to a visualization, or it can be nested as a higher level that can be drilled down into.
The example below is a bar chart that has general ledger information that’s been grouped into higher level categories. For example, the revenue category is made up of several smaller sub-categories. Once they are in a group report viewers can see the aggregated values instead of the line item detail.
Grouping in Power BI is an incredibly useful feature and helps ensure that the reports you are developing tell a good story and are easily understood by report viewers.
Why Use Grouping and Binning in Power BI
Just because you can doesn’t mean that you always should group data together. Grouping should be used when working with large datasets and when individual detail becomes too overwhelming for the average report user to quickly understand and interpret.
By grouping data together in Power BI, you can provide users with a quick easy to understand snapshot and then give them the ability to drill down into the detail if they want to.
One of the biggest benefits of grouping in the report view is that the lower level detail is still available in your report and Power BI can dynamically call it up when a user wants to see it. On the other hand, extremely large datasets may run into performance issues where additional consolidation may be necessary using the Power Query Editor which we will explain in detail later.
How to Group Items Together in Power BI Visuals
To group items together in Power BI, right click on the field that you want to group on the Data Panel of the report view in Power BI and select New Group. This will bring up the Power BI group editor where you can assign individual row values to a specific group by selecting each item, or multi-selecting each and grouping it into a new group.
The Groups Editor in Power BI will automatically show you a list of all of the available field values on the left side of the screen as Ungrouped Values. Left click on each one to select it and click the Group button at the bottom right to create a new group.
Tip: Hold down CTRL while clicking on ungrouped values to select multiple items at one time. This will add multiple items to a group much faster than having to move each one individually.
After pressing the Group Button at the bottom left of the screen, a new group will be created. Group names area automatically assigned, and can be updated by double clicking on the group name.
Items can be removed from groups by selecting them on the right side and clicking Ungroup. Any ungrouped items will remain on the left side in the Ungrouped Values section.
Click OK at the bottom right corner to accept the new groupings. This will generate a new grouped field in the Power BI data panel that can be added to visuals.
Adding New Items to Existing Groups in Power BI
To add an ungrouped value to an existing group, you have to click on the ungrouped value from the Groups Editor and left click on the group you want to move it to on the right-side Groups and members section. When you press the Group button it will move the ungrouped value to the pre-existing group.
Using the Include Other Group Option in Power BI
Power BI developers do not have to assign every row value in a field to a category. By selecting Include Other Groups at the bottom right corner of the Group Editor, Power BI will automatically generate a new group with the name “Other” all ungrouped values will automatically be assigned to the other group.
If you choose to leave this option unselected, only the items that have been assigned groups will be visible to the report viewer when the group is added to a visual. This may or may not be desired behavior, and you will want to be especially careful if data is subject to change, such as an accounting department that frequently adds new GL Accounts.
Assigning Groups to Visuals
After items are grouped together, a new field appears. By default, it will say (groups) at the end of it if you did not update the title of the new grouped field in the group editor. It works the same as any other field and users can drag and drop it into any number of visualizations.
If you add the original field that has line item detail below the grouped values it will become a hierarchy within the visual that has both values assigned to it. In the example below, we used a Matrix visual to show that the line items are listed below the group that they belong to. For other visuals, such as charts you can right click on the chart and select drill down to view the lower level.
Tip: Groups are useful for giving the users the ability to drill down into detail. They are also useful items to add to Power BI Slicers. Slicers are dynamic filters that could allow end users to select a broad category then view the individual detail that makes it up on different parts of the page.
How to Group and Consolidate Columns in Power Query
It’s not always appropriate to group and bin items together using the report view or DAX queries. Large datasets will be slow to refresh and offer a poor user experience. Luckily, it’s possible to setup grouping in Power Query which executes queries during a scheduled refresh.
To group rows together in Power Query, first launch the Power Query Editor. Then select the column that you want to consolidate. Navigate to the Home Section of the Power Query Ribbon, and select Group By to launch the Group By screen.
There are a couple of options when grouping in Power Query, you have to choose which column to group by and consolidate, then you have to assign an aggregation method. Because Power BI will shrink multiple rows into a single row based on common values it needs to know what to do with the other columns.
Power BI supports all of the usual aggregation methods, such as Sum, Count, and Concatenate along with others. The method will determine how values are combined together.
Tip: If you’re unable to choose the aggregation method that you want, such as SUM being unavailable, check the column data type to make sure that an appropriate data type is assigned to the column. Power BI will only be able to SUM columns that are numeric data types.
The Group by screen also supports advanced options, such as grouping by multiple columns and using different aggregation methods for different columns.
Power BI will group columns in order from top to bottom and create a pivot table type hierarchy out of the data.
One thing to keep in mind is that once Power Query consolidates the data, report viewers will not be able to drill into the line item detail that makes it up.
Patrick from Guy in a Cube goes into even more detail about how to setup grouping in Power Query and some of the considerations that you should consider when designing a report and determining the best place to group or bin data.
Both Power Query and grouping in the Report View are powerful tools. Let’s look at one more option that you can use to create dynamically grouped tables using DAX.
How to Dynamically Group Items using Power BI DAX formulas
Power BI developers can use DAX formulas to create new tables of summarized values. It’s a technique that works will with small to medium sized datasets, and is less suitable for large datasets because DAX calculations occur as needed and can cause a poor user experience if the report relies too heavily on DAX summaries.
To group data using DAX formulas in Power BI, start by clicking on New Table on the Table Tools section of the Power BI Ribbon. Because the data is aggregated and results in a table, the formula will not work if you attempt to create it using a New Measure.
The DAX formula that you would enter into the new table is as follows:
Summarized Values = SUMMARIZE('Trial Balance','Account Index'[Account Description],"Summary Table",SUM('Trial Balance'[Value]))The formula contains several components.
- SUMMARIZE – Tells Power BI to Aggregate the Data
- Table Name
- Grouping Column
- Name of New Table
- Aggregation method and Aggregation Column
Each one of these components is separated by commas in the example code above. Once you’ve typed out the formula press enter and it will generate a new table.
Tip: As you’re typing out DAX formulas use the TAB Key to auto-complete field and table names to make sure that you are filling them out correctly. It will also take you to the next stage of the formula as you tab and add commas.
Conclusion
Grouping is an important tool to keep in your Power BI toolbox. You can group items together at the report level using the grouping editor, group and consolidate data using Power Query or you can use DAX formulas to generate temporary tables.
The decision of which one to use when will largely be determined by the size of the dataset and whether or not you are going to want your report viewers to be able to drill down to lower levels of detail in your report.