In this post, I will describe what is the Pearson correlation coefficient and how to implement it in Power BI using DAX.

What is the Correlation Coefficient

The correlation coefficient is a statistical measure of the relationship between two variables; the values range between -1 and 1. A correlation of -1 shows a perfect negative correlation, and a correlation of 1 shows a perfect positive correlation. A correlation of 0.0 shows no linear relationship between the movement of the two variables.

How to interpret the Correlation Coefficient

To go a bit more in detail we can interpret the correlation coefficient as follows:

• -1: Perfect negative correlation
• Between -1 and <=-0.8: Very strong negative correlation
• Between >-0.8 and<=-0.6: Strong negative correlation
• Between >-0.6 and<=-0.4: Moderate negative correlation
• Between >-0.4 and<=-0.2: Weak negative correlation
• Between >-0.2 and<0: Very weak negative correlation
• 0: No correlation
• Between 0 and<0.2: Very weak positive correlation
• Between >=0.2 and <0.4: Weak positive correlation
• Between >=0.4 and <0.6: Moderate positive correlation
• Between >=0.6 and <0.8: Strong positive correlation
• Between >=0.8 and <1: Very strong positive correlation
• 1: Perfect positive correlation

One very important thing to remember is that when two variables are correlated, it does not mean that one causes the other. Correlation does not imply causation

The Formula

Unlike in Excel, there’s no DAX built-in correlation function in Power BI (at the time of writing this post).
In Excel, the built-in function is called Correl, this function requires two arrays as a parameter (X and Y).
The Correl formula used in Excel is as follows:

There are actually several ways of writing the Pearson correlation coefficient formula but to keep consistent with the formula used in Excel I will stick with the above formula which is one of the most common anyway.

Calculate the Correlation Coefficient with DAX

Since we saw the formula above we now need to translate it into DAX.
So let’s break down the formula:

• The Σ (sigma) symbol is used to denote a sum of multiple terms (x1+ x2+x3..) which is an equivalent of sum or sumx
• x̄ (mu x bar), is used to represent the mean of x
• ȳ (mu y bar), is used to represent the mean y
• √ is the square root its dax function is sqrt

Now let’s see the DAX code for the Pearson correlation formula:

coeff corr = 
//x̄
var __muX =calculate(AVERAGE(YourTable[x]))
//ȳ
var __muY=calculate(AVERAGE(YourTable[y]))
//numerator
var __numerator  =  sumx('YourTable',( [x]-__muX)*([y]-__muY))
//denominator
var __denominator=  SQRT(sumx('YourTable',([x]-__muX)^2)*sumx('YourTable',([y]-__muY)^2))
return 
divide(__numerator,__denominator)

Let’s visualise it in Power BI

Let’s now build a small report that will show the correlation between the head size (x) and the brain weight (y).
The data are as follows:

• Head_Size (variable x)
• Brain_Wight (variable y)

In Power BI when clicking on the Analytics icon we can easily add a trend line to visualize the relationship between two variables on a scatter plot.

However, to show the correlation coefficient on top of the trend line we still need to create a DAX measure that I have called “coeff corr”.

And as the final touch let’s create another measure “coeff correl type” that will return the interpretation of the correlation so we can display it on top of our visual.

coeff correl type = 
SWITCH(TRUE,
        [coeff corr]=-1                          ,"Perfect negative correlation",
        [coeff corr]>-1   &&  [coeff corr]<=-0.8 ,"Very strong negative correlation",
        [coeff corr]>-0.8 &&  [coeff corr]<=-0.6 ,"Strong negative correlation",
        [coeff corr]>-0.6 &&  [coeff corr]<=-0.4 ,"Moderate negative correlation",
        [coeff corr]>-0.4 &&  [coeff corr]<=-0.2 ,"Weak negative correlation",
        [coeff corr]>-0.2 &&  [coeff corr]<0     ,"Very weak negative correlation",
        [coeff corr]=0                           ,"No correlation",
        [coeff corr]>0    &&  [coeff corr]<0.2   ,"Very weak positive correlation",
        [coeff corr]>=0.2 &&  [coeff corr]<0.4   ,"Weak positive correlation",
        [coeff corr]>=0.4 &&  [coeff corr]<0.6   ,"Moderate positive correlation",
        [coeff corr]>=0.6 &&  [coeff corr]<0.8   ,"Strong positive correlation",
        [coeff corr]>=0.8 &&  [coeff corr]<1     ,"Very strong positive correlation",
        [coeff corr]=1                           ,"Perfect positive correlation"
)

And this is how things look like when we concatenate our “coeff corr” with the “coeff correl type” measure and add them on top of our scatter plot.

The Power BI

Conclusion

This is just another post on the series of implementing statistical functions in DAX you can read some other similar posts in my blog such as AB testing in Power BI or Poisson distribution in Power BI.

Power BI is still lacking some advanced statistical functions compared to Excel but with DAX we can write almost any existing Excel function!