How to group data by table columns in Power BI?

by

“I’m working with sales data in Power BI and need to group transactions by product category and month to show total sales. I’ve tried using the visual grouping options but can’t get the subtotals to work correctly. How can I properly group my data to show hierarchical summaries?”

While Power BI offers multiple ways to group data, choosing the right approach depends on your specific needs. For a detailed look at working with dates in your groupings, check out our guide on how to group by week and month in Power BI.

Sample Data Model

We will use a dummy Sales table. It contains the following columns: TransactionDate (Date), ProductCategory (Text), ProductName (Text), SalesAmount (Currency), Quantity (Whole Number), and Region (Text). The data shows daily transactions from January 2023 to December 2023.

Grouping Using Power Query

To group data in power query, proceed as following:

  1. Open Power Query Editor
  2. Select the column(s) to group by
  3. Click ‘Group By’ from the Transform tab
  4. Define the following aggregation functions:
  • Sum of SalesAmount
  • Count of transactions
  • Average of Quantity

Here’s the M Code which you can use:

= Table.Group(
    Sales,
    {"ProductCategory", "Region"},
    {
        {"TotalSales", each List.Sum([SalesAmount]), type number},
        {"TransactionCount", each Table.RowCount(_), type number},
        {"AvgQuantity", each List.Average([Quantity]), type number}
    }
)

Using DAX for Dynamic Grouping in Power BI

  1. Create a measure for grouped sales:
Total Category Sales = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    ALLEXCEPT(Sales, Sales[ProductCategory])
)
  1. Add supporting measures:
Category Transaction Count = 
CALCULATE(
    COUNTROWS(Sales),
    ALLEXCEPT(Sales, Sales[ProductCategory])
)
  1. Implement the grouped data in the Power BI Report View :
  • Drag the ProductCategory field to Rows
  • Add measures to Values
  • Enable subtotals in Format pane

Creating Hierarchical Groups

  1. Create a custom hierarchy:
  • Right-click ProductCategory
  • Create Hierarchy
  • Add Region as sublevel
  1. Configure visual settings:
  • Enable “Expand/Collapse” icons
  • Set “Show on row headers”

Alternative Grouping Methods and Advanced Scenarios

Using Matrix Visuals for Multi-Level Grouping

While the table visual works well for basic grouping, the Matrix visual excels at creating complex multi-dimensional groupings. In your Matrix visual, drag ProductCategory to Rows, TransactionDate (set to Month) to Columns, and SalesAmount to Values. This creates a cross-tabulation showing sales by category and month simultaneously. Enable “Show on rows” for subtotals to get automatic category totals, and use “Show on columns” for monthly totals across all categories.

Conditional Grouping with DAX

For dynamic grouping based on business rules, create calculated columns using DAX SWITCH statements:

Sales Tier = 
SWITCH(
    TRUE(),
    Sales[SalesAmount] >= 10000, "High Value",
    Sales[SalesAmount] >= 5000, "Medium Value",
    "Standard Value"
)

This approach lets you group transactions into custom buckets that update automatically as data changes.

Grouping Across Related Tables

When working with normalized data models, leverage relationships for grouping. If your Sales table connects to separate Product and Customer tables, you can group by fields from related tables without merging data. Simply drag fields from related tables into your visual’s grouping areas, and Power BI will automatically traverse the relationships.

Performance Considerations for Large Datasets

For datasets exceeding 100,000 rows, consider pre-aggregating data using Power Query’s Group By function before loading into Power BI. This reduces model size and improves visual performance. Alternatively, use DAX’s SUMMARIZECOLUMNS function in calculated tables for frequently-used groupings:

Sales Summary = 
SUMMARIZECOLUMNS(
    Sales[ProductCategory],
    Sales[Region],
    "Total Sales", SUM(Sales[SalesAmount]),
    "Avg Quantity", AVERAGE(Sales[Quantity])
)

This creates a permanent aggregated table that loads faster than on-demand calculations

Troubleshooting Common Issues

  • Missing Subtotals: Ensure “Subtotals” is enabled in both row and column headers
  • Incorrect Totals: Check if any filters are affecting your grouping
  • Performance Issues: Use SUMMARIZE for large datasets instead of visual-level grouping
  • Blank Groups: Verify data quality and handle null values in Power Query

For more advanced grouping techniques, especially when working with dates, see our guide on grouping by columns and rank in Power BI.