Others

How to Group Duplicate Items and Sum Values in Power BI Report Builder

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs to group duplicate items in a paginated Power BI report, sum their quantities and total prices, and calculate a weighted average unit price.

Product: Power BI Report Builder
Device & OS: not provided
Scenario: Creating or formatting a paginated report where identical items appear on multiple rows with different quantities and prices, requiring aggregation.
Observed behavior: The report currently displays items with the same item code, description, and unit of measure on separate detail rows instead of combining them into a single aggregated row.

Before you start

Ensure you have editing access to the paginated report dataset and are familiar with modifying row groups and expressions in the Report Builder interface.

Solution 1Recommended

Use Row Groups and Aggregate Expressions

Create row groups based on the item attributes and apply mathematical expressions to calculate sums and weighted averages.

In Power BI Report Builder, detail rows display individual records by default. To combine duplicate items, you must group the data in the tablix and replace standard field values with aggregate expressions.

For complex, highly specific enterprise reporting scenarios, it is also highly recommended to consult the Microsoft Power BI Community where specialists can provide tailored guidance.

Add a Parent Group

Right-click the detail row in the Row Groups pane at the bottom of the screen, select 'Add Group' > 'Parent Group', and choose to group by Item Code, Description, and Unit of Measure.

Sum the Quantities and Total Prices

In the cells of the newly grouped row, replace the default field references with sum expressions. For example, right-click the quantity cell, select 'Expression', and enter '=Sum(Fields!Quantity.Value)'.

Calculate the Weighted Unit Price

To get the weighted average, divide the total price by the total quantity. In the Unit Price cell, enter the expression: '=Sum(Fields!TotalPrice.Value) / Sum(Fields!Quantity.Value)'.

Seek Community Assistance

If you encounter errors with your expressions or grouping logic, post your specific dataset structure in the Microsoft Power BI Community for expert troubleshooting.

Data Types Matter: Ensure that your Quantity and Total Price fields are formatted as numeric data types in your dataset, otherwise the Sum function will return an error.

Free Microsoft Office alternative

Easily Group and Aggregate Data with WPS Spreadsheet

While Power BI Report Builder handles complex paginated reports, many everyday grouping and weighted calculation tasks can be handled much faster using spreadsheets. WPS Office provides a lightweight, free alternative to Microsoft Office, offering robust Pivot Tables and data aggregation features that make summarizing duplicate items effortless.

1. Open Your Data: Launch WPS Spreadsheet and open the dataset containing your duplicate items.
2. Insert a PivotTable: Select your entire data range, navigate to the 'Insert' tab, and click 'PivotTable'.
3. Group and Aggregate: Drag 'Item Code' and 'Description' into the Rows area, and 'Quantity' into the Values area to automatically group identical items and sum their totals.

Instantly group duplicate items and sum values using intuitive Pivot Tables.Fully compatible with Microsoft Excel (.xlsx, .xls) and CSV formats.Use built-in functions like SUMPRODUCT for quick weighted average calculations.Free, lightweight, and features a familiar user interface for a seamless transition.

microsoft office alternative - wps office

Frequently Asked Questions

Can I calculate a weighted average directly in a Power BI Report Builder matrix?

Yes. You can achieve this by using an expression in the matrix cell that divides the sum of the total price by the sum of the total quantity, such as =Sum(Fields!TotalPrice.Value) / Sum(Fields!Quantity.Value).

Why are my items not grouping correctly in Report Builder?

Items may fail to group if there are slight discrepancies in the grouping fields, such as hidden trailing spaces or case differences. Ensure your Item Code and Description fields are absolutely identical across all rows.

Where can I get advanced help for Power BI paginated reports?

The official Microsoft Power BI Community forum is the best resource for advanced support, where specialists provide assistance tailored to specific DAX, expression, and grouping scenarios.

Does WPS Office support data grouping similar to Excel?

Yes, WPS Spreadsheet features powerful Pivot Tables, Subtotal tools, and advanced formulas that allow you to aggregate duplicate items and calculate weighted averages effortlessly, just like Microsoft Excel.

Office Quick Solutions