Grouping is a common requirement for summarizing and reporting data. We explore Power BI grouping in this series of videos. This is a companion article for the video series. Ideally, watch the videos before reading this article.
Power BI Grouping
Like with Pivot Tables grouping , Power BI also has grouping features. You can create a group in Data tab, Field list or Relationships tab. Anywhere you see the field name, right click on it gives grouping options.
Grouping on Text data
This is simplistic enough. Choose two or more items and group them. In addition to grouping, you must provide the group name as well.
Text grouping using Power BI Desktop is best done for few groups.
If there are lot of items and lots of groups, this could be a time-consuming process.
In such cases, it is better that you create a lookup table and create a relationship.
Grouping for numbers
Grouping combines many items into few items. With numbers we can have two distinct ways of creating groups. Either you decide where the group starts and then create as many groups as required or you decide how many groups you require and let the system decide where the group is split.
Power BI Hierarchies
Power BI Groups are used on a single column of data. Hierarchies are best used with multiple, related columns of data which form a top-down structure.
Classification of items is the commonest use of Hierarchies. For example, Product Group > Category > Product Name > Packaging > SKU.
Another common use is for representing geography: Country > State > City.
Financial data contains charter of accounts, which is essentially a hierarchy.
To create a hierarchy, drag the related fields one below another. If it has a business benefit, you can drag any fields into a hierarchy.
Usually, it is better to hide the individual columns to avoid duplication.
Benefit of hierarchy is that with one selection, we get the entire structure with drill down capability. It is also more logical to interpret it.
Power BI Groups and Shaping Data
Register with a corporate email id and download Power BI Desktop.