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”

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.