```        []
```
(Showing Draft Content)

# Aggregation Functions

This function helps in calculating a (scalar) value such as count, sum, average, minimum, or maximum for all rows in a column or table as defined by the expression.

Following is the list of aggregation functions supported in Wyn Enterprise:

### AVERAGE

Description

The AVERAGE function returns the average (arithmetic mean) of all the numbers in a column.

Syntax

``AVERAGE(<column>)``

Parameters

Parameter

Description

column

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

Return Value

A decimal number.

Example

``AVERAGE('Sale'[Price])``

The above formula returns the average of the values in the column, Price, in the Sale table.

Remarks

• This function calculates the average of the values in the specified column.

• If the column has non-numeric values, then they are handled as listed below:

• For text values, functions return the blanks.

• Logical values or empty cells are ignored. But the cells having Zero values are included. So, while calculating the average, if a cell has a value of 0, then it is added to the sum of the numbers, and the row is counted among the number of rows used as the divisor. But, when a cell contains a blank value, then the row is not counted.

• If there are no rows to aggregate, then the function will return a blank value. But if there are rows that do not meet the criteria, then the function will return a 0.

Related Function

The AVERAGE function is a simplified version of the AVERAGEX function. AVERAGE(‘Sale’[Price]) is equivalent to AVERAGEX(‘Sale’,‘Sale’[Price])

### AVERAGEX

Description

The AVERAGEX function calculates the average (arithmetic mean) of a set of expressions evaluated over a table.

Syntax

``AVERAGEX(<table>,<expression>)``

Parameters

Parameter

Description

table

Name of the 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.

Return Value

A decimal number.

Example

``AVERAGEX('Sale','Sale'[Price])``

The above formula will first calculate the total amount by multiplying the Price and Quantity in each row and then calculate the average.

Remarks

• This function takes the table as a first argument and the expression as a second argument. So, it will evaluate expressions for each row of a table, and then take the resulting values and calculate its arithmetic mean.

• AVERAGEX follows the same rules as AVERAGE for non-numeric or null cells. It requires both the table and expression arguments.

• If there are no rows to aggregate, then the function will return a blank value. But if there are rows that do not meet the criteria, then the function will return a 0.

### COUNT

Description

The COUNT function counts the number of cells in a column that contain non-blank values.

Syntax

``COUNT(<column>)``

Parameters

Parameter

Description

column

The column that contains the values to be counted.

Return Value

A whole number.

Example

``COUNT('Product'[Name])``

The above example counts the number of values in the column, Name.

Remarks

• Only a Column is allowed as an argument to the COUNT function.

• This function will count rows with the following types of values:

• Numbers

• Dates

• Strings

• If the function finds no rows to count, then it will return a blank.

Related Function

The COUNT function is a simplified version of the COUNTX function. COUNT('Product'[Name]) is equivalent to COUNTX('Product','Product'[Name])

### COUNTROWS

Description

The COUNTROWS function counts the number of rows in the specified table, or a table defined by an expression.

Syntax

``COUNTROWS(<table>)``

Parameters

Parameter

Description

table

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

Return Value

A whole number.

Example

``COUNTROWS('Product')``

The above example will count the number of rows in the table Product.

Remarks

• This function is similar to the following SQL statement:

select count(1) from Product.

• This function can be used to count the number of rows in a base table and also to count the number of rows that result from filtering a table.

• If there are no rows to aggregate, then the function will return a blank value. But if there are rows that do not meet the criteria, then the function will return a 0.

### COUNTX

Description

The COUNTX function counts the number of rows that contain a non-blank value or an expression that evaluates to a non-blank value when evaluating an expression over a table.

Syntax

``COUNTX(<table>,<expression>)``

Parameters

Parameter

Description

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.

Return Value

An integer.

Example

``COUNTX('Product','Product'[Name])``

The above formula returns the count of all the rows in the Product table that has a Name.

Remarks

• This function has two arguments. The first argument should always be a table, or it can be any expression that returns a table. The second argument is the column or an expression searched by COUNTX.

• If the function finds no rows to count, then it will return a blank. COUNTX function counts the values, dates, or strings.

### DISTINCTCOUNT

Description

The DISTINCTCOUNT function counts the number of distinct values in a column.

Syntax

``DISTINCTCOUNT(<column>)``

Parameters

Parameter

Description

column

The column that contains the values to be counted.

Return Value

The number of distinct values in the column.

Example

``DISTINCTCOUNT('Product'[Name])``

This example will count the number of distinct values in the Name column of the Product table.

Remarks

• Only a Column is allowed as an argument to this function.

Columns can have any type of data.

• If the function finds no rows to count, then it will return a blank.

• DISTINCTCOUNT function counts the BLANK values as well.

### DISTINCTCOUNTX

Description

The DISTINCTCOUNTX function counts the number of rows that contain a non-blank distinct value or an expression that evaluates to a non-blank distinct value when evaluating an expression over a table.

Syntax

``DISTINCTCOUNTX(<table>,<expression>)``

Parameters

Parameter

Description

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.

Return Value

The number of distinct values in the column.

Example

``DISTINCTCOUNTX('Product','Product'[Name])``

Remarks

DISTINCTCOUNTNX does not count the BLANK value(s).

### MAX

Description

The MAX function returns the largest value in a column, or between two scalar expressions.

Syntax

``MAX(<column>)``

Parameters

Parameter

Description

column

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

Return Value

The largest value.

Example

``MAX('Sale'[Price])``

This example will return the largest value found in the Price column of the Sale table.

Remarks

• The MAX function is a simplified version of the MAXX function. MAX('Sale'[Price]) is equivalent to MAXX('Sale','Sale'[Price])

### MAXX

Description

The MAXX function evaluates an expression for each row of a table and returns the largest value.

Syntax

``MAXX(<table>,<expression>)``

Parameters

Parameter

Description

table

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

expression

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

Return Value

The largest value.

Example

``MAXX('Sale','Sale'[Price] * 'Sale'[Quantity])``

This formula will use the expression as the second argument to calculate the Sale Amount (Price * Quantity) for each order in the Sale table and then return the highest Sale Amount.

Remarks

• The first argument can be a table name or an expression that evaluates a table. The second argument is an expression to be evaluated for each row of the table.

• Only the following evaluated values are counted:

• Numbers

• Texts

• Dates

• MAXX function skips the Blank values.

### MIN

Description

The MIN function returns the smallest value in a column, or between two scalar expressions.

Syntax

``MIN(<column>)``

Parameters

Parameter

Description

column

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

Return Value

The smallest value.

Example

``MIN('Sale'[Price])``

The above example will return the smallest value found in the Price column of the Sale table.

Remarks

• The MIN function is a simplified version of the MINX function. MIN('Sale'[Price]) is equivalent to MINX('Sale','Sale'[Price]).

• The MIN function takes a column as an argument and returns the smallest value. It will count the following types of values in the columns:

• Numbers

• Texts

• Dates

• Blanks

### MINX

Description

The MIN function returns the smallest value that results from evaluating an expression for each row of a table.

Syntax

``MINX(<table>,<expression>)``

Parameters

Parameter

Description

table

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

expression

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

Return Value

The smallest value.

Example

``MINX('Sale','Sale'[Price] * 'Sale'[Quantity])``

The above formula will first calculate the Sale Amount (Price * Quantity) for each order in the Sale table and then return the smallest Sale Amount.

Remarks

The MINX function takes the table as its first argument or an expression that returns a table. The second argument will have the expression that is evaluated for each row of the table.

### SUM

Description

The SUM function adds all the numbers in a column.

Syntax

``SUM(<column>)``

Parameters

Parameter

Description

column

The column contains the numbers to sum.

Return Value

A decimal number.

Example

``SUM('Sale'[Price])``

The above formula will return the sum of all the values in the Price column of the Sale table.

Remarks

The SUM function is a simplified version of the SUMX function. You can filter the values that you are summing, by using SUMX function and specify an expression to sum over.

### SUMX

Description

The SUMX function returns the sum of an expression evaluated for each row in a table.

Syntax

``SUMX(<table>,<expression>)``

Parameters

Parameter

Description

table

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

expression

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

Return Value

A decimal number.

Example

``SUMX(FILTER('Sale','Sale'[StoreId] = 1),'Sale'[Units Sold] )``

In the above example, the formula will first filter the data in the Sale table for the StoreId column and select the data where StoreId = 1 and then it will add the Units Sold for only the rows where StoreId is 1.

Remarks

• The SUMX function takes a table as its first argument or an expression that returns a table. The second argument is a column containing the numbers you want to sum, or an expression that evaluates to a column.

• This function will count only the numbers in the column. If there are any blanks, logical values, and text, then they will be ignored.