Original article (before wiki edits) was written by Jeannine Takaki and JuanPablo Jofre and formatted by Mary Browning, Microsoft SQL Server Technical Writers.

AVERAGE

Returns the average (arithmetic mean) of all the numbers in a column.

This function takes the specified column as an argument and finds the average of the values in that column. If you want to find the average of an expression that evaluates to a set of numbers, use the AVERAGEX function instead.

Nonnumeric values in the column are handled as follows:

If the column contains text, no aggregation can be performed, and the functions returns blanks.

If the column contains logical values or empty cells, those values are ignored.

Cells with the value zero are included.

When you average cells, you must keep in mind the difference between an empty cell and a cell that contains the value 0 (zero). When a cell contains 0, it is added to the sum of numbers and the row is counted among the number of rows used as the divisor. However, when a cell contains a blank, the row is not counted.

When there are no rows to aggregate, the function returns a blank. However, if there are rows, but none of them meet the specified criteria, the function returns 0. Excel also returns a zero if no rows are found that meet the conditions.

Related Functions

The AVERAGEX function can take as its argument an expression that is evaluated for each row in a table. This enables you to perform calculations and then take the average of the calculated values.

The AVERAGEA function takes a column as its argument, but otherwise is like the Excel function of the same name. By using the AVERAGEA function, you can calculate a mean on a column that contains empty values.

Syntax

AVERAGE(<column>)

column. The column that contains the numbers for which you want the average.

Returns a number that represents the arithmetic mean of the numbers in the column.

Example

The following formula returns the average of the values in the column, ExtendedSalesAmount, in the table, InternetSales.

AVERAGEA

Returns the average (arithmetic mean) of the values in a column. Handles text and non-numeric values.

The AVERAGEA function takes a column and averages the numbers in it, but also handles non-numeric data types according to the following rules:

Values that evaluates to TRUE count as 1.

Values that evaluate to FALSE count as 0 (zero).

Values that contain non-numeric text count as 0 (zero).

Empty text ("") counts as 0 (zero).

If you do not want to include logical values and text representations of numbers in a reference as part of the calculation, use the AVERAGE function.

Whenever there are no rows to aggregate, the function returns a blank. However, if there are rows, but none of them meet the specified criteria, the function returns 0. The function also returns a zero if no rows are found that meet the conditions.

Syntax

AVERAGEA(<column>)

column. The column that contains the values for which you want the average.

Returns a number (R8).

Example

The following example returns the average of non-blank cells in the referenced column, given the following table. If you used the AVERAGE function, the mean would be 21/2; with the AVERAGEA function, the result is 22/5.

AVERAGEX

Calculates the average (arithmetic mean) of a set of expressions evaluated over a table.

The AVERAGEX function enables you to evaluate expressions for each row of a table, and then take the resulting set of values and calculate its arithmetic mean. Therefore, the function takes a table as its first argument, and an expression as the second argument.

In all other respects, AVERAGEX follows the same rules as AVERAGE. You cannot include non-numeric or null cells. Both the table and expression arguments are required.

When there are no rows to aggregate, the function returns a blank. When there are rows, but none of them meet the specified criteria, then the function returns 0.

Syntax

AVERAGEX(<table>,<expression>)

table. Name of a table, or an expression that specifies the table over which the aggregation can be performed.

expression. An expression with a scalar result, which will be evaluated for each row of the table in the first argument.

Returns a number (R8).

Example

The following example calculates the average freight and tax on each order in the InternetSales table, by first summing Freight plus TaxAmt in each row, and then averaging those sums.

If you use multiple operations in the expression used as the second argument, you must use parentheses to control the order of calculations. For more information, see DAX Syntax Specification in the TechNet Library.

COUNT

Counts the number of cells in a column that contain numbers.

The only argument allowed to this function is a column. You can use columns containing any type of data, but only numbers are counted. The COUNT function counts rows that contain the following kinds of values:

Numbers

Dates

If the row contains text that cannot be translated into a number, the row is not counted.

When the function finds no rows to count, it returns a blank. When there are rows, but none of them meet the specified criteria, then the function returns 0.

Syntax

COUNT(<column>)

column. The column that contains the numbers to be counted.

Returns a number (I8).

Example

The following example shows how to count the number of numeric values in the column, ShipDate.

COUNTA

Counts the number of cells in a column that are not empty. It counts not just rows that contain numeric values, but also rows that contain nonblank values, including text, dates, and logical values.

If you do not need to count cells that contain logical values or text, (in other words, if you want to count only cells that contain numbers), use the COUNT or COUNTX functions.

When the function does not find any rows to count, the function returns a blank. When there are rows, but none of them meet the specified criteria, then the function returns 0.

Syntax

COUNTA(<column>)

column. The column that contains the values to be counted.

Returns a number (I8).

Example

The following example returns all rows in the Reseller table that have any kind of value in the column that stores phone numbers. Because the table name does not contain any spaces, the quotation marks are optional.

COUNTAX

Counts nonblank results when evaluating the result of an expression over a table. That is, it works just like the COUNTA function, but is used to iterate through the rows in a table and count rows where the specified expressions results in a nonblank result.

Like the COUNTA function, the COUNTAX function counts cells containing any type of information, including other expressions.

For example, if the column contains an expression that evaluates to an empty string, the COUNTAX function treats that result as nonblank. Usually the COUNTAX function does not count empty cells but in this case the cell contains a formula, so it is counted.

If you do not need to count logical values or text, use the COUNTX function instead.

Whenever the function finds no rows to aggregate, the function returns a blank. However, if there are rows, but none of them meet the specified criteria, the function returns 0. Microsoft Excel also returns 0 if no rows are found that meet the condition.

Syntax

COUNTAX(<table>,<expression>)

table. The table containing the rows for which the expression will be evaluated.

expression. The expression to be evaluated for each row of the table.

Returns a number (I8).

Example

The following example counts the number of nonblank rows in the column, Phone, using the table that results from filtering the Reseller table on [Status] = Active.

COUNTBLANK

Counts the number of blank cells in a column.

The only argument allowed to this function is a column. You can use columns containing any type of data, but only blank cells are counted. Cells that have the value zero (0) are not counted, as zero is considered a numeric value and not a blank.

Whenever there are no rows to aggregate, the function returns a blank. However, if there are rows, but none of them meet the specified criteria, the function returns 0. Microsoft Excel also returns a zero if no rows are found that meet the conditions.

In other words, if the COUNTBLANK function finds no blanks, the result will be zero, but if there are no rows to check, the result will be blank.

Syntax

COUNTBLANK(<column>)

column. The column that contains the blank cells to be counted.

Returns a number (I8). If no rows are found that meet the condition, blanks are returned.

Example

The following example shows how to count the number of rows in the table Reseller that have blank values for BankName.

COUNTROWS

Counts the number of rows in the specified table, or in a table defined by an expression.

This function can be used to count the number of rows in a base table, but more often is used to count the number of rows that result from filtering a table, or applying context to a table.

Whenever there are no rows to aggregate, the function returns a blank. However, if there are rows, but none of them meet the specified criteria, the function returns 0. Microsoft Excel also returns a zero if no rows are found that meet the conditions.

Syntax

COUNTROWS(<table>)

table. The name of the table that contains the rows to be counted, or an expression that returns a table.

Returns a number (I8).

Example

The following example shows how to count the number of rows in the table Orders. The expected result is 52761.

=COUNTROWS('Orders')

The following example demonstrates how to use COUNTROWS with a row context. In this scenario, there are two sets of data that are related by order number. The table Reseller contains one row for each reseller; the table ResellerSales contains multiple rows for each order, each row containing one order for a particular reseller. The tables are connected by a relationship on the column, ResellerKey.

The formula gets the value of ResellerKey and then counts the number of rows in the related table that have the same reseller ID. The result is output in the column, CalculatedColumn1.

COUNTX

Counts the number of rows that contain a number or an expression that evaluates to a number, when evaluating an expression over a table.

The COUNTX function takes two arguments. The first argument must always be a table, or any expression that returns a table. The second argument is the column or expression that is searched by COUNTX.

The COUNTX function counts only numeric values, or dates. Arguments that are logical values or text that cannot be translated into numbers are not counted. If the function finds no rows to count, it returns a blank. When there are rows, but none meets the specified criteria, then the function returns 0.

If you want to count logical values, or text, use the COUNTA or COUNTAX functions.

Syntax

COUNTX(<table>,<expression>)

table. The table containing the rows to be counted.

expression. An expression that returns the set of values that contains the values you want to count.

Returns an integer.

Example

The following formula returns a count of all rows in the Product table that have a list price.

=COUNTX(Product,[ListPrice])

The following formula illustrates how to pass a filtered table to COUNTX for the first argument. The formula uses a filter expression to get only the rows in the Product table that meet the condition, ProductSubCategory = "Caps", and then counts the rows in the resulting table that have a list price. The FILTER expression applies to the table Products but uses a value that you look up in the related table, ProductSubCategory.

MAX

Returns the largest numeric value in a column.

The MAX function takes as an argument a column that contains numeric values. If the column contains no numbers, MAX returns a blank. If you want to evaluate values that are not numbers, use the MAXA function.

Syntax

MAX(<column>)

column. The column in which you want to find the largest numeric value.

Returns a number (R8).

Example

The following example returns the largest value found in the ExtendedAmount column of the InternetSales table.

MAXX

Evaluates an expression for each row of a table and returns the largest numeric value.

The table argument to the MAXX function can be a table name, or an expression that evaluates to a table. The second argument indicates the expression to be evaluated for each row of the table.

Of the values to evaluate, only the following are counted:

Numbers. If the expression does not evaluate to a number, MAXX returns 0 (zero).

Dates.

Empty cells, logical values, and text values are ignored. If you want to include non-numeric values in the formula, use the MAXA function.

If a blank cell is included in the column or expression, MAXX returns an empty column.

Syntax

MAXX(<table>,<expression>)

table. The table containing the rows for which the expression will be evaluated.

expression. The expression to be evaluated for each row of the table.

Returns a number (R8).

Example

The following formula uses an expression as the second argument to calculate the total amount of taxes and shipping for each order in the table, InternetSales. The expected result is 375.7184.

=MAXX(InternetSales, InternetSales[TaxAmt]+ InternetSales[Freight])

The following formula first filters the table InternetSales, by using a FILTER expression, to return a subset of orders for a specific sales region, defined as [SalesTerritory] = 5. The MAXX function then evaluates the expression used as the second argument for each row of the filtered table, and returns the highest cost for tax and shipping for just those orders. The expected result is 250.3724.

MINA

Returns the smallest value in a column, including any logical values and numbers represented as text.

The MINA function takes as argument a column that contains numbers, and determines the smallest value as follows:

If the column contains no numeric values, MINA returns 0 (zero).

Rows in the column that evaluates to logical values, such as TRUE and FALSE are treated as 1 if TRUE and 0 (zero) if FALSE.

Empty cells are ignored.

If you do not want to include logical values and text as part of the calculation, use the MIN function instead.

Syntax

MINA(<column>)

column. The column for which you want to find the minimum value.

Returns a number (R8).

Example

The following expression returns the minimum freight charge from the table, InternetSales.

=MINA(InternetSales[Freight])

The following expression returns the minimum value in the column, PostalCode. Because the data type of the column is text, the function does not find any numeric values, and the formula returns zero (0).

If you want to include logical values and text representations of numbers in a reference as part of the calculation, use the MINA function.

Syntax

MINX(<table>, < expression>)

table. The table containing the rows for which the expression will be evaluated.

expression. The expression to be evaluated for each row of the table.

Returns a number (R8).

Example

The following example filters the table, InternetSales, and returns only rows for a specific sales territory. The formula then finds the minimum value in the column, Freight.

=MINX( FILTER(InternetSales, [SalesTerritoryKey] = 5),[Freight])

The following example uses the same filtered table as in the previous example, but instead of merely looking up values in the column for each row of the filtered table, the function calculates the sum of two columns, Freight and TaxAmt, and returns the smallest value resulting from that calculation.