Aggrigate functions :
When we combine or summarize the numerical data ,it is called Aggrigation.
SUM:
Adds all the numbers in a column.
Syntax:
SUM(<column>)
EX = SUM = SUM(city[salary])
-----------------------------------------------------------------------------------
--------------------------------------------------------------------
SUMX
Returns the sum of an expression evaluated for each row in a table.
Syntax :
SUMX(<table>, <expression>)
ex:SUMX = SUMX(city,city[id]*city[salary])
-----------------------------------------------------------------------------------
----------------------------------------------------------------------
MIN
Returns the smallest value in a column, or between two scalar expressions.
syntax:
MIN(<column>)
Ex:MIN = MIN(city[salary])
-----------------------------------------------------------------------------------
----------------------------------------------------------------------
MINX
Returns the lowest value that results from evaluating an expression for each row of
a table.
Syntax:
MINX(<table>, < expression>,[<variant>])
Example :MINX = MINX(city,city[id]*city[salary])
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------
MAX
Returns the largest value in a column, or between two scalar expressions.
Syntax :
MAX(<column>)
Ex:MAX = MAX(city[salary])
-----------------------------------------------------------------------------------
---------------------------------------------------------------------------
MAXX
Returns the highest value that results from evaluating an expression for each row
of a table.
Syntax:
MAXX(<table>,<expression>,[<variant>])
EX:MAXX = MAXX(city,city[id]*city[salary])
-----------------------------------------------------------------------------------
--------------------------------------------------------------------------
Average :
Returns the average (arithmetic mean) of all the numbers in a column.
Syntax:
AVERAGE(<column>)
Ex: Avg(city[salary])
-----------------------------------------------------------------------------------
-------------------------------------------------------------------------------
AVERAGEX
Calculates the average (arithmetic mean) of a set of expressions evaluated over a
table.
Syntax:
AVERAGEX(<table>,<expression>)
Ex:
AVG X = AVERAGEX(city,city[Qty]*city[unit price ])
-----------------------------------------------------------------------------------
-------------------------------------------------------------------------------
COUNT
Counts the number of rows in the specified column that contain non-blank values.
Syntax:
COUNT(<column>)
Ex:
COUNT = COUNT(Table1[QTY])
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-
COUNTA
Counts the number of rows in the specified column that contain non-blank values.
Syntax:
COUNTA(<column>)
Ex:
COUNT = COUNT(Table1[QTY])
-----------------------------------------------------------------------------------
--------------------------------------------------------------------------------
COUNTROWS
The COUNTROWS function counts the number of rows in the specified table, or in a
table defined by an expression.
syntax:
COUNTROWS([<table>])
Ex:
COUNTROWS('Orders')
-----------------------------------------------------------------------------------
------------------------------------------------------------------------------
COUNTX
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>)
ex:
COUNTX = COUNTX(FILTER(Table1,Table1[IsDeliver]= TRUE()),Table1[QTY])
-----------------------------------------------------------------------------------
-------------------------------------------------------------------------------
DISTINCTCOUNT
Counts the number of distinct values in a column.
syntax:
DISTINCTCOUNT(<column>)
Ex: DISTINCTCOUNT(ResellerSales_USD[SalesOrderNumber])
-----------------------------------------------------------------------------------
----------------------------------------------------------------------------
COUNTBLANK
Counts the number of blank cells in a column.
Syntax :
COUNTBLANK(<column>)
ex: COUNTBLANK(Reseller[BankName])
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
DISTINCTCOUNTNOBLANK (<column>)
ex:
DISTINCTCOUNT(ResellerSales_USD[SalesOrderNumber])
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
COUNTAX
The COUNTAX function counts non-blank 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 non-blank result.
Syntax:
COUNTAX(<table>,<expression>)
EX:COUNT AX = COUNTAX(FILTER(Table1,Table1[IsDeliver]= TRUE()),Table1[IsDeliver])
-----------------------------------------------------------------------------------
---------------------------------------------------------------------------------
