Introduction
I have already covered some statistical functions in
my previous article, and below are some more statistical functions.
Let start this article and have a look at these advanced statistical functions.
Functions and Their Descriptions
NORM.DIST
Returns the normal distribution for the specified mean and standard deviation.
Syntax- NORM.DIST(X, Mean, Standard_dev, Cumulative)
X - The value for which you want the distribution.
Mean -The arithmetic mean of the distribution.
Standard_dev - The standard deviation of the distribution.
Cumulative - A logical value that determines the form of the function. If cumulative is TRUE, NORM.DIST returns the cumulative distribution function; if FALSE, it returns the probability density function.
Ex.
NORM.INV
Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.
Syntax- NORM.INV (Probability, Mean, Standard_dev)
Probability- A probability corresponding to the normal distribution.
Mean - The arithmetic mean of the distribution.
Standard_dev - The standard deviation of the distribution.
Ex.
NORM.S.DIST
Returns the standard normal distribution (has a mean of zero and a standard deviation of one).
Syntax – NORM.S.DIST (Z, Cumulative)
Z –The value for which you want the distribution.
Cumulative –Cumulative is a logical value that determines the form of the function. If cumulative is TRUE, NORM.S.DIST returns the cumulative distribution function; if FALSE, it returns the probability density function.
Ex.
NORM.S.INV
Returns the inverse of the standard normal cumulative distribution. The distribution has a mean of zero and a standard deviation of one.
Syntax- NORM.S.INV(Probability)
Probability- A probability corresponding to the normal distribution.
Ex.
PERCENTILE.EXC
Returns the k-th (exclusive) percentile of value in a column.
Syntax- PERCENTILE.EXC(Column, K)
Column- Any column containing the values that define relative standing.
K-The percentile value in the range 0-1, exclusive.
Note
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
PERCENTILE.INV
Returns the k-th (inclusive) percentile of value in a column.
Syntax- PERCENTILE.INV(Column, K)
Column- A column containing the values that define relative standing.
K- The percentile value in the range 0-1, inclusive.
Note
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
PERCENTILEX.EXC
Returns the k-th (exclusive) percentile of an expression value in a table.
Syntax- PERCENTILEX.EXC(Table, Expression, K)
Table- The table containing the rows for which the expression will be evaluated.
Expression- The expression to be evaluated for each row of the table.
K- The desired percentile value in the range 0 to 1 exclusive.
Note
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
PERCENTILEX.INC
Returns the k-th (inclusive) percentile of an expression value in a table.
Syntax- PERCENTILEX.EXC (Table, Expression, K)
Table- The table containing the rows for which the expression will be evaluated.
Expression- The expression to be evaluated for each row of the table.
K- The desired percentile value in the range 0 to 1 exclusive.
Note
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
POISSON.DIST
Returns the Poisson distribution. A common application of the Poisson distribution is predicting the number of events over a specific time, such as the number of cars arriving at a toll plaza in 1 minute.
Syntax - POISSON.DIST(X, Mean, Cumulative)
X - The number of events. It is required.
Mean - The expected numeric value.It is required.
Cumulative - Required. A logical value that determines the form of the probability distribution returned. If cumulative is TRUE, POISSON.DIST returns the cumulative Poisson probability that the number of random events occurring will be between zero and x inclusive; if FALSE, it returns the Poisson probability mass function that the number of events occurring will be exactly x.
Note
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
RANK.EQ
Returns the rank of a number in a column of numbers. If more than one value has the same rank, the top rank of that set of values is returned.
Syntax -RANK.EQ(Value, ColumnName, [Order])
Value-Any DAX expression that returns a single scalar value whose rank is to be found.
ColumnName-Any column of exiting table against which ranks will be determined.
Order- A value that specifies how to rank number, low to high or high to low. It is optional. Value can be 0 or 1. If it is zero it means ‘FALSE’ and Ranks in descending order of columnName. If value is equal to the highest number in columnName then RANK.EQ is 1. If value is 1 it means ‘TRUE’ and Ranks in ascending order of columnName. If value is equal to the lowest number in columnName then RANK.EQ is 1.
RANKX
Returns the rank of an expression evaluated in the current context in the list of values for the expression evaluated for each row in the specified table.
Syntax- RANKX (Table, Expression,[Value], [Order], [Ties])
Table- Any DAX expression that returns a table of data on which the expression is evaluated.
Expression- Any DAX expression that returns a single scalar value.
Value
Any DAX expression that returns a single scalar value whose rank is to be found.It is optional.\
Order- A value that specifies how to rank value, low to high or high to low. It is optional. Value can be 0 or 1. If it is zero it means ‘FALSE’ and Ranks in descending order of value of expression. If value is equal to the highest number inexpression then RANKX returns 1. If value is 1 it means ‘TRUE’ and Ranks in ascending order of expression.
If value is equal to the lowest number in expressionthen RANKX returns 1.
Ties- An enumeration that defines how to determine ranking when there are ties. It is optional.
Enumeration (Skip)- The next rank value will be the rank value of ties + count of tied. This is the default value if ties omitted.
Enumeration (Dense)-The next rank value will the value of next value of ties.For example if five (7) values are tied with a rank of 13 then the next value will receive a rank of 14.
Note
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
VAR.P
Calculates variance based on the entire population. Ignores logical values and text in the population.
Syntax- VAR.P(ColumnName)
ColumnName- Any existing column but not an expression, using standard DAX syntax, usually fully.
Ex.
VAR.S
Estimates variance based on a sample. Ignores logical value and text in the sample.
Syntax- VAR.S(ColumnName)
ColumnName- Any existing column but not an expression, using standard DAX syntax, usually fully.
Ex.
VARX.P
Estimate variance based on the entire population that results from evaluating an expression for each row of a table.
Syntax- VARX.P(Table, Expression)
Table- Any DAX expression that returns a table of data.
Expression-Any DAX expression that returns a single scalar value, where the expression is to be evaluated multiple times (for each row/context).
VARX.S
Estimates variance based on a sample that results from evaluating an expression for each row of the table.
Syntax- VARX.S(Table, Expression)
Table- Any DAX expression that returns a table of data.
Expression- Any DAX expression that returns a single scalar value, where the expression is to be evaluated multiple times (for each row/context).
Ex.
Summary
Hope you understand all these statistical functions. Try once and let me know if you have any questions.Thanks for reading and have a good day.