Covariance 03-19-2018 08:21 AM - last edited 06-26-2018 09:49 AM

[Greg_Deckler]

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

[sv11]

@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

[Greg_Deckler]

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