Skip to content

01.Aggregation.md

Latest commit

 

History

History
235 lines (167 loc) · 5.1 KB

01.Aggregation.md

File metadata and controls

235 lines (167 loc) · 5.1 KB

Aggregation & Iterator Functions

AVERAGE | AVERAGEA | AVERAGEX
COUNT | COUNTA | COUNTX | COUNTROWS | COUNTBLANK
DISTINCTCOUNT
SUM | SUMX
MAX | MAXX
MIN | MINX

You can compute the Aggregateof one column, but cannot compute aggregate of an expression

Iterator functions can compute aggregate of expression

Aggregation function returns a Scalar value to a column or an expression evaluated by iterating a table expression.

AVERAGE

  • Return the Arithmetic Mean of the values in the column.
  • Return a Scalar value of Currency or Decimal type.
AVERAGE ( 'Table'[Column] )

AVERAGEA

  • Similar to AVERAGE, handles non numeric data.
  • AVERAGEA manages a BOOLEAN data type as an integer ( FALSE : 0 & TRUE : 1 )
  • AVERAGEA always considers a STRING as 0, regardless of the content of the string.

AVERAGEX

  • AVERAGE function internally executes AVERAGEX, without any performance difference.
  • AVERAGEX ignores BLANK()
  • AVERAGEX considers 0
AVERAGEX (
    Table,
    Table[Column]
)

If the Column Values are String

AVERAGEX (
    Table,
    VALUE ( Table[Column] )
)

COUNT

  • Counts the number of Rows in the table.
  • COUNT does not count BLANK() rows, but it counts empty strings.
COUNT ( Table[Column] )

COUNTA

  • Similar to COUNT, can operate on a BOOLEAN data type.

COUNTX

  • Count the number of values which result from evaluating an expression for each row of a table.
  • The COUNT function internally executes COUNTX, without any performance difference.
  • When function finds no rows it returns Blank
COUNTX (
    Table,         // Table
    Table[Column]  // Expression
)

COUNTROWS

  • Counts the number of Rows in the table including blank rows.
  • We can use CALCULATE with COUNTROWS to ignore BLANK() and Empty string.
CALCULATE (
    COUNTROWS ( Table ),
    NOT ISBLANK ( Table[Column] ) && Table[Column] <> "" // Ignore rows with BLANK() and Empty string values.
)

Count the DISTINCT rows in the table

COUNTROWS ( DISTINCT ( Table ) )  or DISTINCTCOUNT ( Table[Column] )

Count whether a column has only one value.

COUNTROWS ( VALUES ( table[column] ) ) = 1 or HASONEVALUE ( table[column] ) )

COUNTBLANK

  • Counts the number of BLANK() rows in the column.
  • It returns BLANK(), if there are no rows.
  • Empty string is considered as a BLANK() for COUNTBLANK
COUNTBLANK ( Table[Column] )

Equivalent faster expression for counting Blank rows.

CALCULATE (
    COUNTROWS ( Table ),
    KEEPFILTERS ( ISBLANK ( Table[Column] ) )
)

Equivalent faster expression for counting rows with Empty strings.

CALCULATE (
    COUNTROWS ( Table ),
    KEEPFILTERS ( Table[Value] = "" )
)

DISTINCTCOUNT

DISTINCT

  • Returns a single column table of Unique Values.
  • Does not return the Blank row.

VALUES

  • Returns a single column table of Unique Values.
  • Similar to DISTINCT but also have additional Blank rows for the values which is not found.
  • SUM accepts only one column, does not support Expression

SUM

  • Add all the numbers in the column.
  • SUM function internally executes SUMX, without any performance difference.
SUM ( Table[Column] )

What actually happens internally

SUMX (
    Table,
    Table[Column]
)

SUMX

  • Returns the sum of an expression evaluated for each row in a table.
  • SUMX supports Expression
SUMX (
    Table,
    Table[Column1] * Table [Column2] // Accepts more than one column and expressions.
)

MAX

  • Returns the Largest value in the column, or larger value between two Scalar expression.
  • Strings are compared according to Alphabetical order.
  • MAX ( Date ) returns the latest date.
  • Ignore Boolean value.
MAX ( Table[Column] )

MAX ( Expression1, Expression2 )

MAXX (
    Table,
    Table[Column]
)

MAXA

  • MAXA operates with Boolean data type, it consider TRUE as 1 and FALSE as 0

MAXX

  • Returns the Largest value that results from evaluating an expression for each row of a table.

MIN

  • Returns the Smallest value in the column, or larger value between two Scalar expression.
  • Strings are compared according to Alphabetical order.
  • MIN ( Date ) returns the oldest date.
  • Ignore Boolean value.
MIN ( Table[Column] )

MIN ( Expression1, Expression2 )

MINX (
    Table,
    Table[Column]
)

MINA

  • MINA operates with Boolean data type, it consider TRUE as 1 and FALSE as 0

MAXX

  • Returns the Smallest value that results from evaluating an expression for each row of a table.