Statistical Functions

Data Analysis Expressions (DAX) provides many functions for creating aggregations such as sums, counts, and averages. These functions are very similar to aggregation functions used by Microsoft Excel. This section lists the statistical and aggregation functions provided in DAX.

In this category

FunctionDescription
ADDCOLUMNSAdds calculated columns to the given table or table expression.
APPROXIMATEDISTINCTCOUNTReturns the approximate number of rows that contain distinct values in a column.
AVERAGEReturns the average (arithmetic mean) of all the numbers in a column.
AVERAGEAReturns the average (arithmetic mean) of the values in a column.
AVERAGEXCalculates the average (arithmetic mean) of a set of expressions evaluated over a table.
BETA.DISTReturns the beta distribution.
BETA.INVReturns the inverse of the beta cumulative probability density function (BETA.DIST).
CHISQ.INVReturns the inverse of the left-tailed probability of the chi-squared distribution.
CHISQ.INV.RTReturns the inverse of the right-tailed probability of the chi-squared distribution.
CONFIDENCE.NORMThe confidence interval is a range of values.
CONFIDENCE.TReturns the confidence interval for a population mean, using a Student’s t distribution.
COUNTThe COUNT function counts the number of cells in a column that contain numbers.
COUNTAThe COUNTA function counts the number of cells in a column that are not empty.
COUNTAXThe COUNTAX function counts nonblank results when evaluating the result of an expression over a table.
COUNTBLANKCounts the number of blank cells in a column.
COUNTROWSThe COUNTROWS function counts the number of rows in the specified table, or in a table defined by an expression.
COUNTXCounts the number of rows that contain a number or an expression that evaluates to a number, when evaluating an expression over a table.
CROSSJOINReturns a table that contains the Cartesian product of all rows from all tables in the arguments.
DATATABLEProvides a mechanism for declaring an inline set of data values.
DISTINCTCOUNTCounts the number of distinct values in a column.
DISTINCTCOUNTNOBLANKCounts the number of distinct values in a column.
EXPON.DISTReturns the exponential distribution.
GENERATEReturns a table with the Cartesian product between each row in table1 and the table that results from evaluating table2 in the context of the current row from table1.
GENERATEALLReturns a table with the Cartesian product between each row in table1 and the table that results from evaluating table2 in the context of the current row from table1.
GEOMEANReturns the geometric mean of the numbers in a column.
GEOMEANXReturns the geometric mean of an expression evaluated for each row in a table.
MAXReturns the largest numeric value in a column, or between two scalar expressions.
MAXAReturns the largest value in a column.
MAXXEvaluates an expression for each row of a table and returns the largest numeric value.
MEDIANReturns the median of numbers in a column.
MEDIANXReturns the median number of an expression evaluated for each row in a table.
MINReturns the smallest numeric value in a column, or between two scalar expressions.
MINAReturns the smallest value in a column, including any logical values and numbers represented as text
MINXReturns the smallest numeric value that results from evaluating an expression for each row of a table.
NORM.DISTReturns the normal distribution for the specified mean and standard deviation.
NORM.INVThe inverse of the normal cumulative distribution for the specified mean and standard deviation.
NORM.S.DISTReturns the standard normal distribution (has a mean of zero and a standard deviation of one).
NORM.S.INVReturns the inverse of the standard normal cumulative distribution
PERCENTILE.EXCReturns the k-th percentile of values in a range, where k is in the range 0..1, exclusive.
PERCENTILE.INCReturns the k-th percentile of values in a range, where k is in the range 0..1, inclusive.
PERCENTILEX.EXCReturns the percentile number of an expression evaluated for each row in a table.
PERCENTILEX.INCReturns the percentile number of an expression evaluated for each row in a table.
POISSON.DISTReturns the Poisson distribution.
RANK.EQReturns the ranking of a number in a list of numbers.
RANKXReturns the ranking of a number in a list of numbers for each row in the table argument.
ROWReturns a table with a single row containing values that result from the expressions given to each column.
SAMPLEReturns a sample of N rows from the specified table.
SELECTCOLUMNSAdds calculated columns to the given table or table expression.
SINReturns the sine of the given angle.
SINHReturns the hyperbolic sine of a number.
STDEV.PReturns the standard deviation of the entire population.
STDEV.SReturns the standard deviation of a sample population.
STDEVX.PReturns the standard deviation of the entire population.
STDEVX.SReturns the standard deviation of a sample population.
SQRTPIReturns the standard deviation of a sample population.
SUMMARIZEReturns a summary table for the requested totals over a set of groups.
T.DISTReturns the Student’s left-tailed t-distribution.
T.DIST.2TReturns the two-tailed Student’s t-distribution.
T.DIST.RTReturns the right-tailed Student’s t-distribution.
T.INVReturns the left-tailed inverse of the Student’s t-distribution.
T.INV.2tReturns the two-tailed inverse of the Student’s t-distribution.
TANReturns the tangent of the given angle.
TANHReturns the hyperbolic tangent of a number.
TOPNReturns the top N rows of the specified table.
VAR.P Returns the variance of the entire population.
VAR.S Returns the variance of a sample population.
VARX.P Returns the variance of the entire population.
VARX.S Returns the variance of a sample population.
XIRRReturns the internal rate of return for a schedule of cash flows that is not necessarily periodic.
XNPVReturns the present value for a schedule of cash flows that is not necessarily periodic.

***** Related Blog Posts *****
60 Page DAX Formula Reference Guide Download Now Available
Show Only Top Or Bottom Results Using Ranking Formula Techniques – Power BI & DAX
How To Calculate The MEDIAN Value In Power BI Using DAX

***** Related Video Tutorials *****
Ultimate Beginners Guide to DAX
What Is DAX? & Why Do You Need To Learn It – (1.2) Ultimate Beginner Guide to DAX
Formula Syntax, Comments & Variables – (1.6) Ultimate Beginners Guide to DAX
Examples Of Advanced DAX – (1.18) Ultimate Beginners Guide to DAX
Calculate The MEDIAN Value In Power BI – Statistical Analysis w/DAX

***** Related Course Modules *****
Ultimate Beginners Guide to DAX
Mastering DAX Calculations
Solving Analytical Scenarios w/ Power BI and DAX
DAX Formula Deep Dives
Advanced DAX Combinations

***** Related Support Forum Posts*****
Mastering DAX Calculations: What Is DAX?
Understanding DAX Formula
Developing Complex Dax functions
Calculate IRR, NPV, IPMT for what if Model
Combining aggregating & iterating functions to calculate median