topic Re: Covariance in Quick Measures Gallery

Covariance

Greg_Deckler — Tue, 26 Jun 2018 16:49:38 GMT

From Wikipedia on Covariance: https://en.wikipedia.org/wiki/Covariance

In probability theory and statistics, covariance is a measure of the joint variability of two random variables.^[1] If the greater values of one variable mainly correspond with the greater values of the other variable, and the same holds for the lesser values, i.e., the variables tend to show similar behavior, the covariance is positive.^[2] In the opposite case, when the greater values of one variable mainly correspond to the lesser values of the other, i.e., the variables tend to show opposite behavior, the covariance is negative. The sign of the covariance therefore shows the tendency in the linear relationship between the variables. The magnitude of the covariance is not easy to interpret because it is not normalized and hence depends on the magnitudes of the variables. The normalized version of the covariance, the correlation coefficient, however, shows by its magnitude the strength of the linear relation.

The covariance formula from http://mathworld.wolfram.com/Covariance.html is the following:

Thus, the following DAX measure equation performs this formula on a table (Covariance) with two columns (A and B).

Covariance = 
VAR AvgA = CALCULATE(AVERAGE('Covariance'[A]),ALL('Covariance'))
VAR AvgB = CALCULATE(AVERAGE('Covariance'[B]),ALL('Covariance'))
VAR MyTable = SUMMARIZE('Covariance','Covariance'[A],"Covariance",([A]-AvgA)*(MAX('Covariance'[B])-AvgB)/CALCULATE(COUNTROWS('Covariance'),ALL('Covariance')))
RETURN SUMX(MyTable,[Covariance])

eyJrIjoiOTU5MWM1M2QtZTdlMy00ZTY4LThkODgtZjU1YzVkOWQ5NjM2IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

Re: Covariance

sv11 — Fri, 08 Feb 2019 19:04:51 GMT

Greg_Deckler

Thanks for providing this information.

I have tried to calculate the Covariance based on your DAX code but the system is throwing an error unkown to me. Could you please take a look at the below DAX code? (Errors in Red color)

Covariance = 
VAR AvgA = CALCULATE(AVERAGE('PeriodSummary-FLEXMUIRRebalancing'[Total Return]), ALL('PeriodSummary-FLEXMUIRRebalancing'))
VAR AvgB = CALCULATE(AVERAGE('PeriodSummary-FLEXMUIRRebalancing'[Benchmark Return]), ALL('PeriodSummary-FLEXMUIRRebalancing'))
VAR MyTable = SUMMARIZE('PeriodSummary-FLEXMUIRRebalancing', 'PeriodSummary-FLEXMUIRRebalancing'[Total Return], "PeriodSummary-FLEXMUIRRebalancing", ('PeriodSummary-FLEXMUIRRebalancing'[Total Return] - AvgA)*(MAX('PeriodSummary-FLEXMUIRRebalancing'[Benchmark Return] - AvgB) / CALCULATE(COUNTROWS('PeriodSummary-FLEXMUIRRebalancing'), ALL('PeriodSummary-FLEXMUIRRebalancing')))
RETURN

Thanks

Re: Covariance

Greg_Deckler — Sat, 09 Feb 2019 00:36:21 GMT

I think you are missing a closing paren, in red

Covariance = 
VAR AvgA = 
CALCULATE(
	AVERAGE('PeriodSummary-FLEXMUIRRebalancing'[Total Return]), 
	ALL('PeriodSummary-FLEXMUIRRebalancing')
)
VAR AvgB = 
CALCULATE(
	AVERAGE('PeriodSummary-FLEXMUIRRebalancing'[Benchmark Return]), 
	ALL('PeriodSummary-FLEXMUIRRebalancing')
)

VAR MyTable = 
SUMMARIZE(
	'PeriodSummary-FLEXMUIRRebalancing', 
	'PeriodSummary-FLEXMUIRRebalancing'[Total Return], 
	"PeriodSummary-FLEXMUIRRebalancing", 
	('PeriodSummary-FLEXMUIRRebalancing'[Total Return] - AvgA)
	*
	(MAX('PeriodSummary-FLEXMUIRRebalancing'[Benchmark Return]) - AvgB) 
	/ 
	CALCULATE(
		COUNTROWS('PeriodSummary-FLEXMUIRRebalancing'), 
		ALL('PeriodSummary-FLEXMUIRRebalancing')
	)
)
RETURN