Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
@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
@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
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])
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |