DAX - Statistical Functions

Question 1

What do statistical functions do?

Answer 1

• Calculate values related to statistical distibutions and probability
• Calculations and comparisons that reveal meaningful information about the data. Such as:
  Trends
  Patterns
  Relationships

Question 2

Common statistical functions

Answer 2

• Average
• Median
• Count
• Distinct(Count)
• Min
• Max

Question 3

How does MAX work?

Answer 3

Returns the largest numeric value or largest string in a column. Ignores logical values.

NOTE: As the MAX function ignores logical values, TRUE and FALSE in the table are interpreted as texts. Texts are considered “larger” than numbers in DAX alphabetically, so the largest value in the column is TRUE, as it’s the last in alphabetical order.

Question 4

How does MIN work?

Answer 4

Returns the smallest numeric value or shortest string in a column. Ignores logical values.

NOTE: As the MIN function also ignores logical values, same as the MAX function, the sequence of blank values, numbers and texts is followed in ascending order. A blank value in the sample column is considered as the
minimum value.

Question 5

How does the average function work?

Answer 5

1. It sums up all the numbers in a dataset
2. Divides the result by the total count of numbers

Question 6

How does the median function work?

Answer 6

1. Sorts the numbers in asc order
2. Selects the middle number
3. If there are two middle numbers, take the average of the two as the median.

For example, if the ItemPrice column in the Sales table contained 2 values,
the formula would be calculated as:
(160 + 180) / 2 = 170

Question 7

Example of AVERAGEX function

Answer 7

AVERAGEX (Sales, Sales[Freight] + Sales[TaxAmt])

In this example:
1. Sales[Freight] + Sales[TaxAmt] is calculated for each row
2. Then the sums are averaged

Question 8

How does AVERAGEX work?

Answer 8

AVERAGEX(Table, Sales[Quantity] * Sales[Price])

It goes row by row and calculates the Sales Amount (quantity x price). Then it calculates an average of Sales Amount amongst all the rows that were calculated

For example, if the ItemPrice column and the QuantitySold column each contained 8 values, the formula would be calculated as:
((120 * 5) + (140 * 7) + (180 * 3) + (130 * 6) + (200 * 4) + (240 * 9) + (160 * 2) + (190 * 8)) / 8 = 962.50

Question 9

What does AVERAGEA do?

Answer 9

AVERAGEA([ColumnName)
Calculates the average of non blank cells in a column

NOTE
-Values that evaluate to TRUE count as 1.
-Values that evaluate to FALSE count as 0 (zero).
-Values that contain non-numeric text count as 0 (zero).
-Empty text (“”) counts as 0 (zero).

For example, if the ItemPrice_withText column contained 8 values, with non-numeric values being counted as 0, the formula would be calculated as:
(120 + 1 + 0 + 130 + 180 + 0 + 280 + 1) / 8 = 89

Question 10

Is the Median or Average less affected by outliers and extreme values?

Answer 10

Median

It is therefore useful for datasets with skewed distributions

Question 11

What is returned if there are no rows to count using COUNT or DISTINCTCOUNT

Answer 11

(blank)

Question 12

What does count do?

Answer 12

Counts the total number of rows, containing any or no value

Question 13

What does COUNTBLANK do?

Answer 13

Counts the number of blank or empty rows in a column

Question 14

How does COUNTA work?

Answer 14

It counts the number of cells in a column that are not blank

Question 15

How does COUNTAX work?

Answer 15

Goes row by row and and counts rows where the specified expression results in a non-blank result.

Question 16

COUNTAX syntax

Answer 16

COUNTAX(<table>, <expression>)

Example
COUNTAX (FILTER (Reseller, [Status] = "Active"), [Phone])

In the example above, it counts the number of non-blank rows in the phone column using the table that results from filtering the reseller table on Status=Active

Question 17

What does COUNTROWS count?

Answer 17

=COUNTROWS([‘Orders’])
=COUNTROWS(RELATEDTABLE(ResellerSales))

It counts the number of rows in the specified table or a table defined by an expression

Question 18

How do MINA or MAXA work?

Answer 18

• Gets the smallest or largest value in a column and doesn’t ignore logical values and text.
• This could therefore be used to get the smallest or largest date. MIN and MAX only work on numbers.

Question 19

MINX syntax

Answer 19

MINX(<table>, <expression>)

Question 20

How does MINX work?

Answer 20

MINX (FILTER (Sales, [SalesTerritoryKey] = 5), [Freight])

An expression is evaluated for each table row and then the smallest value is returned.

In this example, the Sales table is filtered for SalesTerritoryKey 5. Then the function gets the MIN freight for territory 5.

Question 21

How does the MAXX function work?

Answer 21

MAXX (Sales, Sales [TaxAmt]+ Sales [Freight])

This function evaluates an expression for each row and then returns the largest value.

In the above example, the tax plus frieght is calculated for each row and then the max value is returned.

Question 22

MAXX syntax

Answer 22

MAXX(<table>, <expression>)

Question 23

Variance of a Sample Population

Answer 23

VAR.S(<column>)

Returns the variance of a column containing a sample population.

Just writing a long sentence so it will left justify

Question 24

Example Variance using sample population

Answer 24

Assuming the column on which the variance/deviation will be calculated refers to a sample population:

Example: VAR.S(Sales[ItemPrice]) = 1.629

Question 25

Standard Deviation of sample population

Answer 25

~~~
STDEV.S(<column>)
~~~</column>

Returns the standard deviation of a column containing a sample population.

Question 26

Example Standard Deviation using sample population

Answer 26

Assuming the column on which the variance/deviation will be calculated refers to a sample population:

Example: STDEV.S(Sales[ItemPrice])= 40

Question 27

Variance of Entire Population

Answer 27

VAR.P(<column>)

Returns the variance of a column, assuming that the column refers to the entire population.

Question 28

Example Variance using entire population

Answer 28

Assuming the column on which the variance/deviation will be calculated contains the entire population of data points:

Example: VAR.P(Sales[ItemPrice]) = 1.425

Question 29

Standard Deviation of entire population

Answer 29

STDEV.P(<column>)

Returns the standard deviation of a column containing an entire population.

Question 30

Example Standard Deviation using entire population

Answer 30

Assuming the column on which the variance/deviation will be calculated contains the entire population of data points:

Example: STDEV.P(Sales[ItemPrice]) = 38

Question 31

Combination Function

Answer 31

COMBIN(n,k)

Returns the number of ways k items can be selected from n, where the order DOES NOT matter.

Example: COMBIN(49,6) = 13.983.816

Question 32

Permutation Function

Answer 32

PERMUT(n,k)

Returns the number of ways k items can be selected from n, where the order DOES matter.

Example: PERMUT(49,6) = 10.068.347.520