SUMX Function DAX

SUMX Function is a power bi mathematical function in DAX 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.

DAX SYNTAX

SUMX(<table>, <expression>)

<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.

Following is the sample table which consist a products supplied by the suppliers along with total quantity and per quantity amount by category and subcategory.

Now your requirement is to display a overall quantity that will be a quantity * per quantity amount  supplied by individual suppliers .

Can it be done by SUM function ?

SUM function operates over a single column of data to aggregate all the data in that single column with the current filters applied. But you requirement is different as you need to evaluate multiple columns Quantity and PerQtyAmount in a table through row by row to calculate the Overall Quantity by individual supplier.

Lets try to SUM function for this requirement using following cases:

Specify a table name and expression: It gives an error as SUM function accepts only single argument.

Specify only expression: It still gives an error as sum function accepts column as reference not an expression.

So, if you have a such requirement, in this case you can use SUMX function which can be operated on multiple columns in a table through row by row evaluation in those columns.

Using a SUMX function

OverAllQtySum = 
SUMX(SupplierMaster, SupplierMaster[Quantity]*SupplierMaster[PerQtyAmount])

Lets commit the measure OverAllQtySum and drag into visual to see the output.

Now you can see the output returned by measure in below screenshot.

It returns the total sum of supplied quantity for each supplier by evaluating the sum for quantity * perQtyAmount row by row through the specified table.




Also Read..

SUM

DIVIDE

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 403 total views,  8 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.