SUMX() Function is a power bi DAX mathematical function that returns the sum of an expression evaluated for each row in a table. It returns a decimal number.
It goes through a table, row by row to complete the evaluation after filter is applied.
Only the numbers in the column are counted. Blanks, logical values, and text are ignored.
It can be used to evaluate expression over multiple columns as it has the ability to work row by row.
<table> is a base table for which an expression will be evaluated.
<expression> is an expression to be evaluated for each row of the table.
Lets go through an example to see the SUMX() function in action.
Here we have a sample table which consits a products supplied by suppliers along with product’s qunatity and per qunatity amount for individual category , subcategory.
Now our requirement is to display a overall qunatity (that is quantity * per quantity amount) supplied by individual suppliers .
Can it be done by SUM() function ?
DAX SUM() function operates over a single column of data to aggregate all the data in that single column with the current filters applied.
but as per our requirement we have to evalualte multiple column that is Quantity and PerQuantityAmount through row by row through the table.
Lets try to SUM() function for this requirement using following cases:
Pass a table name and expression – It gives an error as SUM() function accepts only single argument
Pass only expression – It gives an error as sum() function accepts column as reference not an expression
So, if we have a such requirement in this case we can use SUMX() function which can be operated on multiple columns in a table throughrow by row evaluation in those columns.
Lets create a measure
OverAllQtySum = SUMX(SupplierMaster,SupplierMaster[Quantity] *SupplierMaster[PerQtyAmount])
Once you commit the DAX, a OverAllQtySum measure is created .
Now you can see the outptut returned by measure in below screenshot.
It returns the total sum of supplied quantity for individual supplier by evaluting the sum for quantity * perQtyAmount row by row through the specified table.