cancel
Showing results for 
Search instead for 
Did you mean: 

Covariance

Super User
1079 Views
Highlighted
Super User
Super User

Covariance

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:

 

covariance.gif

 

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])

 

 

 

 

 

 

 

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


sv11 Regular Visitor
Regular Visitor

Re: Covariance

@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

Super User
Super User

Re: Covariance

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 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!