Power BI Grouping is the process of combining or merging two or more values for further analysis. For example, When we are seeing products by category report, we may find very few records (unnoticeable products). Sometimes, it may be annoying to see all those under performing products. In this situation you can create a group by combining those records, and display it as one product. In this article we will show you, How to create groups in Power BI reports with example
For this demonstration we are going to use the SQL Data Source that we created in our previous article. So, Please refer Connect to SQL Server article to understand the Data Source.
Create Groups in Power BI
To visually demonstrate this grouping technique, we will create a Pie chart. As you can see from the below screenshot, we created a Pie chart by dragging the Sales Amount column to Values field, and Color to Legend region.
If you observe the below screenshot, Data that we highlighted such as Multi, NA, and Blue color products are not performing well. So, let us group them
To do so, Please select the fields that you want to group, and right-click on it will open the context menu. Pleas select Group option from the menu.
Once you Click on the Group option, Power BI will automatically group those items as shown below. That’s it, we done the grouping.
If you observe closely, Legend section is replaced by Color group, and color is placed in details section.
TIP : By default, Power BI Group option will group the selected items as one group, and remaining options as another group.
Let me remove the Color field from the Details section. Now you can see only two partitions in the Pie chart: one contains Blue, White and NA colors, and second partition contains remaining colors.
Edit Power BI Group
Please select the group that you want to edit, and right-click on it will open the context menu. Select the Edit Groups option form it
Clicking the Edit Groups option will open the following window.
- Name: Specify the Group name
- Field: Field or Column that you used for grouping
- Group Type: List (group) or bin.
- Ungrouped values: Fields that are not grouped but available in this column.
- Groups and Members: List of group, and their members.
- Include Other Group: Do you want to place all the ungrouped members in the Other group. If so ,check mark this option.
Let me uncheck the Include Other group. This will remove others group
Now you can see the pie chart that is displaying all the colors, and one group that contains Blue, Multi and NA colors.
Remove Power BI Group Members
Please select the group member that you want to remove, and click ungroup button. For the demo purpose, we are removing the Blue color.
Now you can see that the Blue color is removed, and added under the ungrouped values
And our pie chart is displaying all the colors, and one group that contains Multi and NA colors.
Rename a Group in Power BI
Double-click on the group that you want to rename, and provide a valid name. Let me rename it as White & Multi & NA
From the below screenshot, you can see that the group name is renamed.
Add a Member to Groups in Power BI
Please select the group member that you want to add, and select the group as well. Next, click on the Group button.
For the demo purpose, we are adding White color to existing group.
Now you can see the White color under the group
Create a New Group in Power BI
Please select the field for which you want to create a group, and click group button. For the demo purpose, we are selecting Yellow color.
Now you can see the new group.
Let me add few more fields to it
Now you can see, our pie chart has four colors, and they are: Black, Red, and two groups
Once you created a Group, it will act as any other column or fields. So, you can use it in N number of charts.
To demonstrate the same, we created a Column chart that shows the Country wise sales.
Next, we added the Color group in the legend section.
Second approach to create Groups in Power BI
Please select the filed that you want to use as a group, and right-click on it will open the context menu. Select the New Group option from the context menu.
This will open the following window
Sometimes, Power Bi will open the same window with different option like we shown in the below screenshot. But, don’t worry, you can change the Group type from Bin to List
Once you changed that property, you are good to go