Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to use SUMX and RELATED Dax functions to iterate and return related value in another table. Let’s get started
SUMX function returns the sum of an expression evaluated for each row in a table
SUMX Syntax =SUMX(table, expression)
RELATED function returns a related value from another table
RRELATED Syntax =RELATED(ColumnName)
In the captions below, we want to calculate the Total Revenue by using SUMX and RELATED functions to connect to the Price in the Product_Category table and multiplied by the Units in the Transaction table
In the Calculations group of the Modelling tab, click on New Measure
In the formula bar, execute the dax formula:
Total Revenue = SUMX(‘Transaction’,RELATED(Product_Category[Price])*’Transaction'[Units])
From the capture above, we can see the explicit DAX Measure in the Fields
Next, we create Matrix visualization and drag and drop Product Category and Total Revenue into the Rows and Values axis respectively
We can see the Total Revenue for each of the Product Category
