Others

Fix DAX Relationships for Multiplying Units by Gross Value in Excel

Aamir Naveed Akram Oct 23, 2026 868 views

Question details

The user needs to calculate gross revenue by multiplying the 'Units' in a sales table by the 'Gross' value in a related car_models table using DAX.

How to Fix DAX Relationships for Multiplying Units by Gross Value

Product: Microsoft Excel / Power BI
Device & OS: not provided
Scenario: Calculating accurate gross revenue and profit by multiplying fields across related tables in a Data Model.
Observed behavior: Simply creating a relationship between two tables does not automatically perform row-by-row multiplication for pivot tables; a specific iterative measure is required.

Before you start

Ensure that your Data Model has an active one-to-many relationship established between your lookup table (e.g., car_models) and your fact table (e.g., sales), and that the lookup table has unique keys.

Solution 1Recommended

Use SUMX and RELATED Functions for Row-by-Row Calculation

This is the standard and most robust method to multiply values across related tables by iterating over the fact table.

A standard relationship alone only filters data; it does not automatically calculate row-by-row math across tables. To perform this, you need an iterator function like SUMX combined with the RELATED function to pull the corresponding lookup value.

Open the DAX measure editor

Navigate to the Power Pivot or Power BI measure creation window to create a new measure for Total Gross.

Write the SUMX formula

Use the SUMX function to iterate through your sales table row by row. The first argument will be your fact table (sales).

Use RELATED to fetch the Gross value

Complete the formula by multiplying the Units column by the Gross column from the related table: Total Gross = SUMX(sales, sales[Units] * RELATED(car_models[Gross])).

Use SUMX and RELATED Functions for Row-by-Row Calculation

Verify Model Keys: Make sure your car_models table has exactly one unique row for each model key to prevent relationship errors.

Solution 2

Calculate Profit Across Multiple Related Tables

Extend the SUMX logic to include a third table, such as a profit margin table, for more complex financial calculations.

Solution 3

Alternative: Aggregate First and Iterate Over the Lookup Table

Useful when you prefer to base your calculations on existing base measures rather than raw table columns.

Free Microsoft Office alternative

Need a Fast and Reliable Spreadsheet Tool? Try WPS Office

While advanced DAX data modeling is specific to Microsoft's Power BI and Power Pivot, WPS Office provides a free, highly compatible, and robust spreadsheet application for your everyday data analysis, pivot tables, and reporting needs without the heavy resource requirements.

1. Download WPS Office: Visit the official WPS Office website and download the installation package for your operating system.
2. Install the suite: Run the installer and follow the on-screen instructions to set up WPS Spreadsheet, Writer, and Presentation.
3. Open your data files: Launch WPS Spreadsheet and open your existing .xlsx files to continue summarizing and analyzing your data with ease.

Completely free and lightweight suite for everyday tasksHigh compatibility with Microsoft Excel (.xlsx) and standard formulasFamiliar user interface for seamless and quick migrationPowerful standard Pivot Tables for rapid data summarization

Frequently Asked Questions

Why doesn't linking tables in the Data Model automatically multiply the columns?

Creating a relationship between tables allows for data filtering across those tables, but it does not dictate mathematical operations. You must explicitly define how the data should be calculated (e.g., row-by-row multiplication) using DAX functions like SUMX.

What is the difference between SUM and SUMX in DAX?

SUM is an aggregation function that simply totals the values in a single column. SUMX is an iterator function that evaluates an expression row by row across a specified table before summing up the final results, making it essential for multiplying values from different columns.

When should I use the RELATED function?

The RELATED function is used when you are in a row context (such as inside a SUMX function or a calculated column) and need to retrieve a corresponding value from another table that is linked via a many-to-one relationship.

Why are my Grand Totals incorrect when multiplying two separate measures?

If you simply multiply two aggregation measures (e.g., [Total Units] * [Total Gross]), the engine multiplies the grand total of units by the grand total of gross at the bottom line, rather than summing up individual row calculations. Using SUMX ensures the multiplication happens at the row level before summing.

Office Quick Solutions