Power BI automatically works with categorical data to calculate measures and aggregations at the appropriate category level. Most datasets contain categorical columns that can be used to filter, slice, and organize data. However, the categories provided by your data source may not always organize information in the way your report needs. In those situations, Power BI provides grouping tools that let you create your own classifications.
Creating list groups
A list group is a grouping column where you manually organize existing values into categories. For example, imagine you want to analyze sales by season. The sample data contains a field combining the customer's region with the month of the sale. You can select that column and use the Data Groups options to create your own groups. Power BI allows you to select combinations of values and assign them to a group. Because Australia is in the Southern Hemisphere, for instance, December, January, and February can be grouped as summer for Australian sales. You can continue creating groups for autumn, winter, and spring. Any values you don't manually group will pass through as their existing values by default. Alternatively, you can enable an Other group to combine all remaining ungrouped values into a single category.
Understanding the limitations of list groups
List groups are useful when you're working with a relatively small and predictable collection of values, but they have two important limitations. First, you need to manually classify the values in the column. Second, you can only group values that already exist in your dataset. For example, suppose there haven't been any January sales in the Central United States. Because that combination doesn't currently exist in the data, you can't manually include it in your January grouping. If a January sale is eventually recorded for that region, it won't automatically belong to the appropriate group. Someone would need to update the grouping manually, or the new value would remain ungrouped or appear under Other. For scenarios where new combinations may appear in the future, a custom grouping column can be a better solution.
Creating custom groups with DAX
Instead of manually grouping every possible value, you can create a new column and use DAX to define your grouping logic. For the seasonal sales example, you can create a column called Season and use a SWITCH statement to determine the season based on the month and sales region. The formula can account for regional differences. December, January, and February can return summer for Australia, while returning winter for other regions. The same logic can then be extended across all four seasons. The advantage is that your grouping logic can handle future data automatically. If a new sale appears for a region and month combination that wasn't previously represented in the dataset, the DAX logic can still classify it correctly. Custom grouping with DAX can be especially useful when:
- Your data contains many possible values.
- New values or combinations may appear later.
- Your grouping depends on multiple conditions.
- Manually maintaining every group would be inefficient.
For smaller datasets where all possible values already exist, Power BI's built-in grouping tools may still be the simpler option.
Grouping numerical data into bins Power BI's built-in grouping capabilities are also useful for numerical data. For example, individual sales order totals can be difficult to interpret when they're plotted directly. A few frequently occurring order values can dominate the visual and make it harder to understand the overall distribution of sales. Binning groups numerical values into ranges, making the distribution easier to analyze. When you create a data group from a numerical field, Power BI automatically selects Bin as the group type. You can define bins in two primary ways:
- Size: Specify how large the range of each bin should be.
- Number of bins: Specify how many bins you want, and let Power BI calculate their size.
Power BI can initially calculate a suggested bin size for you. You can accept that recommendation or enter your own value. For example, setting the bin size to $100 lets you see how order totals are distributed across $100 increments.
Using bins to create a histogram
After creating the bins, you can use them in a visual to produce a clearer histogram of your order totals. Instead of displaying every individual sales total, the visual shows how frequently orders fall within each range. You can adjust the grouping at any time. Increasing or decreasing the bin size changes the level of detail in the resulting distribution. Alternatively, specify the number of bins you want Power BI to display and allow Power BI to determine an appropriate range for each one. Choosing the best approach depends on how much detail you need and how much room you have in your visual.
Working with hierarchies
Hierarchies organize related fields into levels that users can navigate within Power BI visuals. You've probably already encountered hierarchies when working with dates. When you add a date to a visual, Power BI can automatically create a hierarchy containing levels such as:
You can then navigate up or down through these levels or expand the hierarchy to see increasingly detailed information.
Creating your own hierarchy
You can also create hierarchies from fields in your own dataset. For example, product information might naturally follow this structure:
- Category
- Subcategory
- Product name
You can create this structure manually in a visual simply by adding each field in the appropriate order. However, if you frequently use the same combination of fields across multiple visuals, creating a reusable hierarchy can save time. Start with the highest level of the hierarchy, such as Category. Right-click the field in the Data pane and select Create hierarchy. Then add each subsequent level, such as Subcategory and Product Name, to that existing hierarchy. Once created, the hierarchy can be added to a visual as a single reusable object rather than requiring you to add each individual field every time.
Why create custom hierarchies?
Custom hierarchies can make report development more efficient and provide a more consistent experience across visuals. They're especially valuable when you're building a Power BI dataset that other people will use. Instead of requiring every report creator to understand which fields belong together and manually recreate the structure, you can provide a predefined hierarchy they can easily add to their reports. This makes common relationships within your data easier to understand, reuse, and explore.
Choosing the right Power BI grouping method
Power BI offers multiple ways to organize data, and the best method depends on what you're trying to accomplish. Use list groups when you have a manageable set of existing values that can be classified manually. Use custom DAX grouping columns when your classifications need to accommodate future data or more complex logic. Use bins when you want to organize numerical values into ranges and visualize their distribution. Use hierarchies when you have fields with natural levels that users will frequently navigate or reuse across visuals. Together, data groups and hierarchies give you practical ways to augment your data and make it easier to display, navigate, and understand in Power BI.
