SUM DAX Function (Aggregation)

Adds all the numbers in a column.

Syntax

SUM ( <ColumnName> )
Parameter Attributes Description
ColumnName

The column that contains the numbers to sum.

Return values

Scalar A single value of any type.

Result of the sum.

Remarks

When used with a single column, the SUM function internally executes SUMX, without any performance difference.
The following SUM call:

SUM ( table[column] )

corresponds to the following SUMX call:

SUMX (
    table,
    table[column] 
)

The result is blank in case there are no rows in the table with a non-blank value.

» 1 related function

Examples

--  SUM is the short version of SUMX, when used with one column only
--  SUMX is required to evaluate formulas, instead of columns
DEFINE
    MEASURE Sales[# Quantity 1] = SUM ( Sales[Quantity] )
    MEASURE Sales[# Quantity 2] = SUMX ( Sales, Sales[Quantity] )
    MEASURE Sales[Sales Amount] =
        SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
EVALUATE
SUMMARIZECOLUMNS (
    'Product'[Color],
    "Quantity 1", [# Quantity 1],
    "Quantity 2", [# Quantity 2],
    "Sales Amount", [Sales Amount]
)
Color Quantity 1 Quantity 2 Sales Amount
Silver 27,551 27,551 6,798,560.86
Blue 8,859 8,859 2,435,444.62
White 30,543 30,543 5,829,599.91
Red 8,079 8,079 1,110,102.10
Black 33,618 33,618 5,860,066.14
Green 3,020 3,020 1,403,184.38
Orange 2,203 2,203 857,320.28
Pink 4,921 4,921 828,638.54
Yellow 2,665 2,665 89,715.56
Purple 102 102 5,973.84
Brown 2,570 2,570 1,029,508.95
Grey 11,900 11,900 3,509,138.09
Gold 1,393 1,393 361,496.01
Azure 546 546 97,389.89
Silver Grey 959 959 371,908.92
Transparent 1,251 1,251 3,295.89
--  SUMX is needed to iterate the content of a variable,
--  indeed SUM works only with columns in the model
DEFINE
    MEASURE Sales[Sales Amount] =
        SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
    MEASURE Sales[SUM Monthly Sales] =
        VAR MonthlySales =
            ADDCOLUMNS (
                DISTINCT ( 'Date'[Calendar Year Month] ),
                "@MonthlySales", [Sales Amount]
            )
        VAR FilteredSales =
            FILTER ( MonthlySales, [@MonthlySales] > 10000 )
        VAR Result =
            -- Iterator required to aggregate the @MonthlySales column        
            SUMX ( FilteredSales, [@MonthlySales] )
        RETURN
            Result
EVALUATE
SUMMARIZECOLUMNS ( 
    'Product'[Color], 
    "SUM Monthly Sales", [SUM Monthly Sales] 
)
Color SUM Monthly Sales
Silver 6,798,560.86
Blue 2,435,444.62
White 5,829,599.91
Red 1,095,460.51
Black 5,860,066.14
Green 1,390,853.24
Orange 797,481.72
Pink 782,566.58
Grey 3,509,138.09
Silver Grey 275,285.20
Brown 1,006,993.45
Gold 245,278.91
Yellow 25,048.62

Related functions

Other related functions are:

Last update: Jul 30, 2021   » Contribute   » Show contributors

Contributors: Alberto Ferrari, Marco Russo

Microsoft documentation: https://docs.microsoft.com/en-us/dax/sum-function-dax

2018-2021 © SQLBI. All rights are reserved. Information coming from Microsoft documentation is property of Microsoft Corp. » Contact us   » Privacy Policy & Cookies

Context Transition

This function performs a Context Transition if called in a Row Context. Click to read more.

Row Context

This expression is executed in a Row Context. Click to read more.

Iterator

Not recommended

The use of this function is not recommended. See Remarks and Related functions for alternatives.

Not recommended

The use of this parameter is not recommended.

Deprecated

This function is deprecated. Jump to the Alternatives section to see the function to use.

Deprecated

This parameter is deprecated and its use is not recommended.

DirectQuery compatibility

Limitations are placed on DAX expressions allowed in measures and calculated columns.
The state below shows the DirectQuery compatibility of the DAX function.

Contribute

Want to improve the content of SUM? Did you find any issue?
Please, report it us! All submissions will be evaluated for possible updates of the content.


This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.