Creating Co-variance using DAX

[rampsaladi]

Hi Can anyone help me in getting the covariance statistics function using dax. The following is an example series.

A	B
1	11
2	12
3	13
4	14
5	15
6	16
7	17
8	18
9	19
10	20

Based on the Covariance formula I did an individual average subtracted from the values and mutipled them and divided by the toal N. The problem I see that when i do the Average of the series and use that average to subtract from each value of the series the values I get are different from the values I get when i do the same thing in Excel. Any help in this regard is appreciated

[Greg_Deckler]

@rampsaladi - One question, where did you get that formula for covariance, because it is not necessarily jiving with what I am seeing here:

https://en.wikipedia.org/wiki/Covariance

https://math.tutorvista.com/statistics/covariance.html

[rampsaladi]

@Greg_Deckler Thanks for looking it up quickly, I know there is a Covariance function in Power Query but was not sure how to use it as I have never used the M and as far as the formula is concerend I got it from the below site. 

http://mathworld.wolfram.com/Covariance.html

I do have the expected value for the co-variance so I will use the DAX that you provided and see how it matches up 

[Greg_Deckler · Accepted solution]

OK, I created a Quick Measure gallery entry here if you want to follow-up on this:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Covariance/m-p/379069#M115

[Greg_Deckler]

This DAX gives me the same number that I get in Excel, 8.25 for the information given:

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

[Greg_Deckler]

There is a List.Covariance function in Power Query. I will look into this for DAX, do you have what the result should be for the information provided?