Power BI Ad Hoc Grouping

One of the exciting features recently added to Power BI is ad hoc grouping. Released at the end of October 2016 and then improved upon at the end of November 2016, ad hoc grouping allows us to create one or more groups of items while visualizing data, view totals for that group as a whole, and compare those totals with values for other ad hoc group and individual items.

We have always been able to use attributes and hierarchies in our Power BI data models to view measures aggregated across certain characteristics. However, the whole point of an ad hoc data exploration tool, such as Power BI, is to be able to follow the data wherever it leads. At times, that means viewing data aggregated in a way that was not anticipated when the data model was created. In the past, that meant switching from visualization back to make changes to the data model. Now, we can create ad hoc groups to complete our ad hoc visualization.

Creating an Ad Hoc Group

Creating an ad hoc group is very straightforward. For example, I have a data model containing Gross Domestic Product (GDP) information for countries/territories since 1960. My data model allows me to view GDP by continent and by various predefined political and economic groups such as the European Union (EU), Group of 7 nations (G7), etc. Suppose, given the Brexit vote, I want to compare the GDP of the United Kingdom with the GDP of the rest of the EU. My model is not set up for that.

Instead, I need to create an ad hoc group containing the members of the European Union except for the UK. Here’s how it can be done using the new ad hoc grouping feature:

1. I’ll start with a chart showing GDP by country for the members of the EU. (I’ve added a slicer by year as well.)
   
2. Click the column for Germany to select it.
3. While holding down the Ctrl key, click the bars for all of the other countries aside from the United Kingdom to add them to the select.
4. Right-click one of the selected bars and select Group from the context menu.
   
5. The selected items are now combined in an ad hoc grouping. An entry for the ad hoc grouping is created in the Fields area.
   
6. Uncheck the Country/Territory item in the Fields area so only the ad hoc group is shown in the chart. We now see a yellow bar representing the combined GDP of our grouping and a dark gray bar representing other countries that satisfy the current filter condition (EU=True), but are not in our ad hoc set. In this case, the “Other” bar represents the United Kingdom because it is the only EU country not in our ad hoc group.
   

Refining the Ad Hoc Group

The chart shown at the end of the previous section allows us to do the comparison we set out to do. However, it is not very self-explanatory, so not really fit to share with anyone else. Once an ad hoc group has been created, Power BI allows us to refine that group to make it more user friendly.

1. Right-click the “Country/Territory (groups)” entry in the Fields area and select “Edit Groups.”
   
2. In the Groups dialog box, change Name from “Country/Territory (groups)” to “EU without UK vs North America”.
3. Scroll through the Ungrouped values and select “United Kingdom”.
4. Click the Group button to create a new grouping that contains only the UK.
5. Now let’s create a North America group for comparison. In the Ungrouped values list, select “United States of America”.
6. Scroll through the Ungrouped values list to find Mexico. Hold down CTRL and click Mexico to select it in addition to the United States.
7. Scroll through the Ungrouped values list again to find Canada. Hold down CTRL and click Canada to select it in addition to the United States and Mexico.
8. Click the Group button to create a new grouping of these three countries.
9. The name of this new grouping is highlighted. Type “North America” and press ENTER.
10. Unfortunately, we can’t change the name of our existing grouping. To do this, we need to ungroup and regroup these countries. Fortunately, that is an easy task. Select the “Austria & Belgium & Croatia & Cyprus & Czech Re…” grouping in the Groups and members list.
11. Click the Ungroup button. These countries are ungrouped and moved to the Ungrouped values list, but they all remain selected.
12. Click the Group button to create a new grouping.
13. The name of this new grouping is highlighted. Type “EU without the UK” and press ENTER. Minimize the “EU without the UK” group so we can see all of the groups in the Groups and members list.
14. Click OK to exit the Groups dialog box.
    
15. Note that our filter condition is still in effect, so our North America grouping does not show up in the chart. In the Filters area, click the “X” next to the EuropeanUnionMember entry.
    
16. The chart is now much easier to read.
    

Deleting an Ad Hoc Group

If you no longer need a particular ad hoc group you created, simply right-click the group in the Fields area and select Delete from the context menu.

Newly Empowered Ad Hoc Analysis

As you can see, Power BI ad hoc grouping is a very powerful tool for on-the-fly analysis without requiring changes to the underlying data model.