Forum Discussion
Creating Co-variance using DAX
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
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
5 Replies
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?
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])rampsaladi - One question, where did you get that formula for covariance, because it is not necessarily jiving with what I am seeing here:
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
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
