# POWER BI- STATISTICAL FUNCTION

In this article, we learn about the statistical function. Data Analysis Expressions (DAX) provides many functions for creating aggregation such as sum, count, average.

AVERAGE

- The AVERAGE function returns the average of all the numbers in a column.
- If the column contains logical values or empty cells those values are ignored.
- If the column contains text then the function returns the error there is no aggregation performed.

**Syntax:- **AVERAGE(<column>)

**Query:-** AVERAGE(‘PRODUCT’[AMOUNT])

**Output:- **It has calculated the average of the Amount column. Here we see the average is 11000.

AVERAGEX

- The AVERAGEX function calculates the average of a set of expressions evaluated over a table.

**Syntax:- **AVERAGEX(<table>,<expression>)

**Query:-**

AVERAGEX(‘PRODUCT’,’PRODUCT’[QUANTITY] * ‘PRODUCT’[AMOUNT])

**Output:- **In the following example, we do multiplication of Quantity and Amount in the product table.

COUNT

- The COUNT function counts the number of cells in a column that contain numbers.
- The COUNT function counts rows that contain the Numbers, Dates, and Strings.

**Syntax:- **COUNT(<column>)

**Query:- **COUNT(‘PRODUCT’[ADDRESS])

**Output:- **Here we count the Address column.

COUNTX

- 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>)

**Query:-**

COUNTX(FILTER(‘PRODUCT’,’PRODUCT’[ADDRESS]=” ”),[QUANTITY])

**Output:- **Here we count blank value of the address table.

DISTINCTCOUNTNOBLANK

- DISTINCTCOUNTNOBLANK function counts the number of distinct values in a column.
- DISTINCTCOUNTNOBLANK does not include a blank value.

**Syntax:- **DISTINCTCOUNTNOBLANK (<column>)

**Query:- **DISTINCTCOUNTNOBLANK(‘PRODUCT’[QUANTITY])

**Output:- **In the following example, we count distinct quantity.

DATA TABLE

- Datatable provides a mechanism for declaring an inline set of data values.

**Syntax:- **DATATABLE (ColumnName1, DataType1, ColumnName2, DataType2…, {{Value1, Value2…}, {ValueN, ValueN+1…}…})

**Query:- **DataTable(“Name”, STRING, { {“VAISHALI”} } )

**Output:-**

GEOMEAN

- It returns the geometric mean of the numbers in a column.
- It counts only the numbers in the column. Blanks, logical values, and text are ignored.

**Syntax:- **GEOMEAN(<column>)

**Query:- **GEOMEAN(‘PRODUCT’[AMOUNT])

**Output:- **The following example will provide the geometric mean of the Quantity column in the Product table.

GEOMEANX

- GEOMEAN function is similar to GEOMEANX.
- GEOMEANX returns the geometric mean of an expression evaluated for each row in a table.

**Syntax:- **GEOMEANX(<table>, <expression>)

**Query:- **GEOMEANX(‘PRODUCT’,’PRODUCT’[QUANTITY]+5)

**Output:- **The following example will provide the geometric mean of the quantity column in the Product table. But here we set the expression on the quantity column.

MAX

- MAX function returns the largest value in a column, or between two scalar expressions.

**Syntax:- **MAX(<column>) **OR** MAX(<expression1>, <expression2>)

**Query:- **MAX(SALES[AMOUNT],SALES[SALES_AMOUNT])

**Output:- **MAX returns the largest value between the Amount and Sales_Amount column in the Sales table.

MAXX

- MAXX function evaluates an expression for each row of a table and returns the largest value.

**Syntax:- **MAXX(<table>,<expression>)

**Query:- **MAXX(FILTER(‘PRODUCT’,’PRODUCT’[PROD_ID]>=104),’PRODUCT’[QUANTITY]*’PRODUCT’[AMOUNT])

**Output:- **In the following example first Filter table as per expression it return a value from product table. Then MAXX function return largest value.

MIN

- MIN function returns the smallest value in a column, or between two scalar expressions.

**Syntax:- **MIN(<column>) **OR **MIN(<expression1>, <expression2>)

**Query:- **MIN(SALES[AMOUNT],SALES[SALES_AMOUNT])

**Output:- **MIN returns the smallest value between the Amount and Sales_Amount column in the Sales table.

MINX

- MINX function returns the smallest value that results from evaluating an expression for each row of a table.

**Syntax:- **MINX(<table>, < expression>)

**Query:- **MINX(FILTER(‘PRODUCT’,’PRODUCT’[PROD_ID]>=104),’PRODUCT’[QUANTITY]*’PRODUCT’[AMOUNT])

**Output:- **In the following example first Filter table as per expression it return a value from product table. Then MINX function return smallest value.

ADDCOLUMNS

- Adds calculated columns to the given table or table expression.

**Syntax:- **ADDCOLUMNS(<table>, <name>, <expression>[, <name>, <expression>]…)

**Query:- **ADDCOLUMNS(‘PERSON’,”TOTAL_SALES”, SUMX(SALES,SALES[AMOUNT]))

**Output:- **The following example returns total_sales table that includes sum of the amount from the sales table.

NORM.DIST

- Returns the normal distribution for the specified mean and standard deviation.

**Syntax:- **NORM.DIST(X, Mean, Standard_dev, Cumulative)

**Query:- **NORM.DIST(42, 40, 1.5, TRUE)

**Output:- **The normal distribution for the specified mean and standard deviation.

BETA.DIST

- The beta distribution is commonly used to study variation in the percentage of something across samples.

**Syntax:- **BETA.DIST(x,alpha,beta,cumulative,[A],[B])

**Parameter:-**

**x:-**The value between A and B at which to evaluate the function.**Alpha:-**A parameter of the distribution.**Beta:-**A parameter of the distribution.**A:-**Optional. A lower bound to the interval of x.**B:-**Optional. An upper bound to the interval of x.

**Query:-**

- BETA.DIST(0.5,9,10, TRUE(),0,1)
- BETA.DIST(0.5,9,10, FALSE(),0,1)

**Output:-**

If you are new to Power BI start with the following must-watch video:-