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.
Contents
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
Learn more about Power BI Data shaping and Power BI Desktop. Power BI desktop allows shaping in the form of data categories, groups, hierarchies, sort by column, synonyms and more.
Register with a corporate email id and download Power BI Desktop.
3 Responses
Sir,
Every tip, is being used n everyday work and the meaning of productivity is getting ushered multi-fold. Request to add more clarity on excel table and graphs as well (e.g. how to draw graph when X and Y axis are Texts only, Why external parties are prevented from adding in TEAMS by some organisations citing reasons of data security. How to view shared folder or files in Windows File Explorer ONE DRIVE hierarchy.
Thanks for great inputs. Will work on creating the content. Cheers. Doc.
Pl bring out a chapter on drill down features