Function Problems

How to Sum Matching Product IDs Using Power Pivot DAX Formula

Maira Mehtab Sep 22, 2026 871 views

Question details

The user needs to create a calculated column in Power Pivot using a DAX formula to sum values based on a condition where a target product ID matches the source product ID from the current row.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Calculating conditional sums across rows in a dataset loaded into the Power Pivot Data Model.
Observed behavior: Requires a specific DAX formula to correctly filter the dataset row-by-row and aggregate the total count for matching product IDs, accounting for multiple occurrences.

Before you start

Ensure your dataset is correctly loaded into the Power Pivot Data Model and that the column names in your formula strictly match the headers in your dataset.

Solution 1Recommended

Use CALCULATE and EARLIER in a DAX Calculated Column

Combine the CALCULATE, FILTER, and EARLIER functions in a DAX calculated column to evaluate conditions row-by-row and sum the respective data.

In Power Pivot DAX, traditional Excel functions like SUMIFS cannot be used directly in calculated columns for row-by-row filtering against the table itself. Instead, the CALCULATE function modifies the filter context, while EARLIER allows the formula to reference a value from the current row before evaluating the rest of the table.

Open Power Pivot

Open your Excel workbook, navigate to the 'Power Pivot' tab on the ribbon, and click on 'Manage' to open the Power Pivot window.

Add a New Calculated Column

Navigate to the 'Data' view of your table. Click on the first cell in the empty column on the far right labeled 'Add Column'.

Enter the DAX Formula

In the formula bar at the top, enter the following DAX expression: =CALCULATE(SUM(Data[FROM_COUNT2]), FILTER(Data, Data[TO_PRODUCTID] = EARLIER(Data[FROM_PRODUCTID])))

Apply and Rename

Press Enter to apply the formula. The column will populate with the summed values. You can then right-click the column header to rename it appropriately.

Important Note on Table Names: In the provided formula, 'Data' refers to the name of the table. If your table has a different name, such as 'Sales' or 'Inventory', be sure to replace 'Data' with your actual table name throughout the formula.

Free Microsoft Office alternative

Easily Analyze Data with WPS Spreadsheet

While Power Pivot and DAX are exclusive features of Microsoft Excel, WPS Office provides a robust, lightweight, and free alternative for comprehensive data analysis. With powerful standard formulas and dynamic PivotTable functionalities, you can easily filter, summarize, and evaluate your data without complex coding.

1. Download and Install WPS Office: Get WPS Office for free from the official website and install it on your device.
2. Open Your Data File: Launch WPS Spreadsheet and open your existing .xlsx or .csv dataset files without losing formatting.
3. Analyze Your Data: Use standard functions like SUMIFS or insert a built-in PivotTable from the 'Insert' tab to achieve similar data summarization without needing DAX.

Seamlessly compatible with Microsoft Excel formats (.xlsx, .xls) and standard formulas like SUMIFS.Built-in PivotTable features for fast and dynamic data summarization and reporting.Lightweight installation with smooth performance on Windows, Mac, and Linux.Free to use with a highly familiar user interface, requiring zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get an error saying EARLIER is not valid in this context?

The EARLIER function relies on an existing row context. This error typically happens if you try to use the formula in a 'Measure' instead of a 'Calculated Column'. Make sure you are adding the formula to a new column in the table.

Can I achieve this sum without Power Pivot using regular Excel formulas?

Yes, in a standard worksheet, you can achieve this by using the SUMIFS function. For example: =SUMIFS(B:B, C:C, A2), where B contains the values to sum, C contains TO_PRODUCTID, and A2 is the current row's FROM_PRODUCTID.

What does the CALCULATE function do in this formula?

The CALCULATE function evaluates an expression (in this case, SUM) in a modified filter context. Here, it applies the specific filters defined by the FILTER function before summing the values.

Excel & Data Problems