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