Way back in Oct-2016, Microsoft released a feature in Power BI Desktop that makes the process of creating groups much easier for the average user. I was reminded of this feature by an article by Reid Havens a couple of months ago. I blogged about various other ways of doing this, including banded columns using DAX here, and then about Conditional Columns in Power Query (Power BI Desktop version) here. But the first of these required writing DAX to arrive at the solution and the second one required knowledge of Power Query. You can learn both of these with proper guidance and once you master the skills you can solve any business problem. This blog post shows you how to use the grouping feature in Power BI Desktop for text or numbers in the Report view itself.
Grouping Values in a Text Column
Consider the Color column in the Products table in AdventureWorks database. Products can have different colours and there may be a requirement to group the colours into Light Colours and Dark Colours, for example. Of course you can do this with a calculated column using DAX or adding a conditional column in Power Query as I previously explained. But with the Power BI Desktop feature “New group“, you can do this easily in the Report view itself using the UI.
- Right-click on the color column (#1 below) in the Fields list and select New group (#2 below).
This opens the Groups dialog box as shown below, and lists all the values in the color column.
- Select all the dark colours using Ctrl Click, or Shift Click (#1 below) and then click the Group button (#2 below).
The selected colours get added under Groups and members (#1 below).
- Name the group as Dark Colours (#2 below).
- Select all the light colours (#1 below), click on Group and then name the Group as Light Colours (#2 below).
- Check the box Include Other group (#1 below).
A group by name Other (#2 below) gets created that will contain all the previously un-grouped values. This step is optional. Should the list of values grow in the future, the new items will be added to “Other”.
Now you can add the newly created group (#1 below) to your Matrix (#2 below) and the colours grouped as above will be displayed (#3 below).
It is not immediately obvious, but after you create a group, there doesn’t seem to be any way to edit it. But there IS a trick. First click on the group name on the right, then hold Shift and click on any item from the left (that you forgot to add to the group), then click the group button.
Grouping Values in a Numeric Column
Suppose you are interested to know the Total Sales by Customer age. In the Customers table, you have BirthDate column. You can add a calculated column Age as follows.
Age = DATEDIFF(Customers[BirthDate], TODAY(), YEAR)
Next, try to plot the data in a Clustered Column chart.
As you can notice, the chart is quite noisy and it is difficult to deduce anything from that.
One solution for this is to have age groups on the x-axis instead of age itself. And it can easily be done by using the grouping feature in the Report view itself.
- Right-click on the Age column and select New group.
The Groups dialog box in the case of numeric column (Age) is as shown below.
You can notice that the Group type is selected as Bin (#1), Bin Type as Size of bins (#2), and Bin size as 10. You can also see that the min (#4) and max (#5) values in the column are displayed.
You can now add the Age(Bins) group thus created to the x-axis in place of Age.
Your clustered column looks as below.
This chart shows the distribution of age groups in a way that is easier to consume and understand.