SUMX
Evaluates an expression on every row of a table and adds the results up.
Group: Aggregation. Compatible with DAX.
Syntax
SUMX ( <table>, <expression> )| Parameter | Type | Description |
|---|---|---|
table | Table | The rows to go through: a table of the model or any table expression. |
expression | Expression evaluated for each row | The number worked out for each row. |
Returns
Number. The total of the expression's results.
Remarks
- The expression sees each row in turn, so it can combine columns of that row, and follow relationships with
RELATED. - A measure in the expression is evaluated with the row as its filter (context transition):
SUMX ( VALUES ( Products[Product] ), [Liters sold] )adds each product's liters. - Blank results are left out; when every result is blank, or the table is empty, the answer is BLANK.
- A result of text or TRUE/FALSE is an error. Turn text into a number with
VALUEfirst.
Examples
Revenue of a few order lines
Liters times price, row by row; the Cider line has no liters and adds nothing.
EVALUATE
VAR Orders =
DATATABLE (
"Product", STRING, "Liters", INTEGER, "Price", DOUBLE,
{ { "Pils", 120, 38 }, { "IPA", 80, 64 }, { "Cider", BLANK (), 52 }, { "Stout", 45, 70 } }
)
RETURN
ROW ( "Revenue", SUMX ( Orders, [Liters] * [Price] ) )| Revenue |
|---|
| 12830 |
Revenue by product, from the model
EVALUATE
SUMMARIZECOLUMNS (
Products[Product],
"Revenue", SUMX ( Sales, Sales[Liters] * RELATED ( Products[Price per liter] ) )
)
ORDER BY Products[Product]| Products[Product] | Revenue |
|---|---|
| Cider | 15655848 |
| IPA | 21240576 |
| Lettøl | 5075370 |
| Pils | 38164388 |