POWER BI: Modeling

Statistical Functions

Standard Deviation functions

Standard deviation is used to measure the amount of spread or dispersion in a data set. There are two types: population standard deviation and sample standard deviation. The population standard deviation is a fixed value (i.e specific number) that can be calculated when the entire population of the data you are interested in is available. The sample standard deviation is needed when you only have a sample of data from a specific population. For example, if you want to predict how people will vote in the next national election, you need to use the sample standard deviation (as you will not be able collect info on every single voter).

This demo illustrates the different standard deviation functions you can write in Power BI. On how to summarize data quickly see summarizing data in Power BI.

There are four types of standard deviation functions:

• STDEV.P(column)
• STDEV.S(column)
• STDEVX.P(table,expression)
• STDEVX.S(table,expression)

STDEV.P

Function returns population standard deviation for values in a column.

Example Formula in Power BI

                  stDev_pop = STDEV.P(TransactionData[Revenue])
	           

STDEV.S

Function returns sample standard deviation for values in a column.

Example Formula in Power BI

                 stDev_sample = STDEV.S(TransactionData[Revenue])
	            

STDEVX.P

Function returns population standard deviation for an expression evaluated row by row over table.

Example Formula in Power BI

                 stDevX_pop = STDEVX.P(TransactionData,TransactionData[Revenue]-TransactionData[Cost])
	            

STDEVX.S

Function returns sample standard deviation for an expression evaluated row by row over table.

Example Formula in Power BI

                 stDevX_sample = STDEVX.S(TransactionData,TransactionData[Revenue]-TransactionData[Cost]) 
	            

Example:

Calculate the sample standard deviation of daily sales for each month:

Answer:

Define the following measure in Power BI:

                 STDEV of Sales = STDEVX.S(CalenderDate,CALCULATE(SUM(TransactionData[Revenue])))
	            

Afterward select as values the date column in your calendar date table and the measure create above. Remove the day field in the Values section so it only contains year, quarter and month.