Function Problems

How to Use DAX to Sum Related Power Pivot Values with a Filter in Excel

Nimra Malik Oct 14, 2026 869 views

Question details

The user needs to calculate the total estimated hours from one table for each corresponding activity in another table using Power Pivot DAX formulas.

Product: Excel
Device & OS: not provided
Scenario: Calculating sums across related tables in an Excel Power Pivot Data Model when creating calculated columns or measures.
Observed behavior: The goal is to accurately sum related rows based on matching activity IDs, which requires establishing a table relationship or using an explicit DAX filter formula.

Before you start

Ensure both tables are loaded into your Excel Data Model and verify that the columns used for matching (e.g., Activity and OP) have identical data types, as mismatched formats will cause calculation errors.

Solution 1Recommended

Use CALCULATE and SUM with an Established Table Relationship

The most efficient way to sum related data in DAX is to first build a relationship between the tables so DAX can automatically filter the context.

By defining a relationship in the Data Model, you eliminate the need for complex FILTER functions, allowing DAX to handle row context dynamically.

Create a Data Model Relationship

Navigate to the Power Pivot tab on the Excel ribbon, click 'Manage' to open the Data Model, and switch to 'Diagram View'. Drag the matching column (e.g., Activity) from one table to the matching column (e.g., OP) in the second table.

Add a Calculated Column

Switch back to 'Data View' and select the main table where you want the totals to appear. Click on 'Add Column' on the far right.

Enter the DAX Formula

In the formula bar, type =CALCULATE(SUM(IW37Ntab[Estimated hrs])) and press Enter. The column will populate with the summed values for each matching activity.

Use CALCULATE and SUM with an Established Table Relationship

Best Practice: If you plan to use this result in a PivotTable, creating a DAX Measure instead of a Calculated Column is highly recommended for better performance.

Solution 2

Use the Explicit FILTER and EARLIER Functions (Without Relationship)

If you cannot create a direct relationship between the tables, you can explicitly filter the target table within your DAX formula.

Free Microsoft Office alternative

Use WPS Spreadsheet for Standard Data Analysis Without Complex DAX

While advanced Power Pivot and DAX modeling are proprietary features of Microsoft Excel, WPS Office provides a free, highly compatible alternative for standard PivotTables, SUMIFS formulas, and everyday data analysis tasks. Enjoy a lightweight application with a familiar interface and seamless migration for your spreadsheet workflows.

1. Download and Install WPS Office: Visit the official WPS website to download the free office suite and complete the quick installation process.
2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing .xlsx workbook to begin analyzing your data.
3. Analyze Data Using Built-in Tools: Instead of relying on DAX, easily summarize related data using standard SUMIFS formulas or by inserting a classic PivotTable.

Seamlessly compatible with Microsoft Excel (.xlsx, .xls) files and standard formulas.Easily create PivotTables and utilize powerful built-in aggregation functions like SUMIFS.100% free with a lightweight, user-friendly interface that feels familiar.Cross-platform support for Windows, Mac, Linux, iOS, and Android.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my DAX calculate formula return an error or blank values?

This typically occurs due to a data type mismatch between the related columns. Ensure both columns (e.g., Activity and OP) are set to the exact same data type (such as Text or Whole Number) in the Power Pivot window, and check for hidden spaces.

Should I use a calculated column or a measure to sum related Power Pivot values?

Use a measure if you plan to display the aggregated results dynamically in a PivotTable, as it computes efficiently on the fly based on user filters. Use a calculated column if you need to evaluate row-by-row data or use the sum as a slicer/category.

What does the EARLIER function do in my DAX formula?

The EARLIER function tells DAX to refer to the value in the current row of your starting table before applying the evaluation to the related table. It acts as a logical bridge to match rows when no formal table relationship exists.

Excel & Data Problems