- Getting Started
- Administration Guide
-
User Guide
- An Introduction to Wyn Enterprise
- Document Portal for End Users
- Data Governance and Modeling
- Working with Resources
- Working with Reports
- Working with Dashboards
- View and Manage Documents
- Understanding Wyn Analytical Expressions
- Section 508 Compliance
- Subscribe to RSS Feed for Wyn Builds Site
- Developer Guide
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.