How To Get Correlation Coefficient In Power BI

There are many kinds of correlation coefficients, but Pearson’s correlation coefficient is the most popular. It is used in linear regression. It is also used to measure the relationship between two variables.The value of a correlation coefficient is always between -1 to 1.
 
Where,
  • 1 indicates a strong positive relationship
  • -1 indicated a strong negative relationship
  • 0 indicates a no relationship between two values
The below graph images will help you to understand the positive, negative and no correlation.
 
How To Get Correlation Coefficient In Power BI
 

How to Calculate Correlation Coefficient

 
To find the correlation coefficient you need to add a quick measure (see https://www.c-sharpcorner.com/article/what-is-quick-measure-in-power-bi/).First, we need to import the table so that we can add a quick measure into it.
 
I am going to import data from SQL server (see here).
 
Go to home tab click on the get data drop down and then click on ‘sql server’.
 
How To Get Correlation Coefficient In Power BI 
 
A new window will be opened, provide your server name and database name and connect your sql server by clicking on ‘Ok’.
 
How To Get Correlation Coefficient In Power BI 
 
Once you connect your server a navigator window will be opened. Select your table and click on load.Your table will be loaded.
 
How To Get Correlation Coefficient In Power BI 
 
Loaded table will be shown at the right side of the tool.
 
How To Get Correlation Coefficient In Power BI 
 
Now go to home tab and click on ‘Quick Measure’. A new quick measure window will be opened.
 
How To Get Correlation Coefficient In Power BI 
 
Click on drop down menu of ‘Select a calculation’ and go to ‘Mathematical Operations’ and click on ‘Correlation coefficient’.
 
How To Get Correlation Coefficient In Power BI 
 
Calculate correlation coefficient between two values over the category. You have to provide three data fields:
  1. Category - Category to find the correlation over
  2. Measure X - The first measure to find the correlation between
  3. Measure - The second measure to find the correlation between
How To Get Correlation Coefficient In Power BI 
 
Click on ‘Ok’ to calculate the correlation coefficient. After clicking on ‘Ok’ a new measure ‘OrderQty and UnitPrice correlation for ProductID’ will be created in the table. The background calculation will be shown while you click on this newly added quick measure.
  1. OrderQty and UnitPrice correlation for ProductID =   
  2. VAR __CORRELATION_TABLE = VALUES('Sales SalesOrderDetail'[ProductID])  
  3. VAR __COUNT =  
  4.     COUNTX(  
  5.         KEEPFILTERS(__CORRELATION_TABLE),  
  6.         CALCULATE(  
  7.             SUM('Sales SalesOrderDetail'[OrderQty])  
  8.                 * SUM('Sales SalesOrderDetail'[UnitPrice])  
  9.         )  
  10.     )  
  11. VAR __SUM_X =  
  12.     SUMX(  
  13.         KEEPFILTERS(__CORRELATION_TABLE),  
  14.         CALCULATE(SUM('Sales SalesOrderDetail'[OrderQty]))  
  15.     )  
  16. VAR __SUM_Y =  
  17.     SUMX(  
  18.         KEEPFILTERS(__CORRELATION_TABLE),  
  19.         CALCULATE(SUM('Sales SalesOrderDetail'[UnitPrice]))  
  20.     )  
  21. VAR __SUM_XY =  
  22.     SUMX(  
  23.         KEEPFILTERS(__CORRELATION_TABLE),  
  24.         CALCULATE(  
  25.             SUM('Sales SalesOrderDetail'[OrderQty])  
  26.                 * SUM('Sales SalesOrderDetail'[UnitPrice]) * 1.  
  27.         )  
  28.     )  
  29. VAR __SUM_X2 =  
  30.     SUMX(  
  31.         KEEPFILTERS(__CORRELATION_TABLE),  
  32.         CALCULATE(SUM('Sales SalesOrderDetail'[OrderQty]) ^ 2)  
  33.     )  
  34. VAR __SUM_Y2 =  
  35.     SUMX(  
  36.         KEEPFILTERS(__CORRELATION_TABLE),  
  37.         CALCULATE(SUM('Sales SalesOrderDetail'[UnitPrice]) ^ 2)  
  38.     )  
  39.   
  40. RETURN  
  41.     DIVIDE(  
  42.         __COUNT * __SUM_XY - __SUM_X * __SUM_Y * 1.,  
  43.         SQRT(  
  44.             (__COUNT * __SUM_X2 - __SUM_X ^ 2)  
  45.                 * (__COUNT * __SUM_Y2 - __SUM_Y ^ 2)  
  46.         )  
  47.     )  
To check the value of this correlation coefficient, select a ‘Card’ visual from the visualization panel and select this newly-added measure. You can see that the value of correlation coefficient lies between -1 to 1. See the below image,
 
How To Get Correlation Coefficient In Power BI 
 

Summary

 
This is how you can calculate the correlation coefficient between two values. I hope you understand how to find correlation coefficient.This is also very useful for linear regression. I will write a separate article on linear regreesion. So, stay tuned. Thanks for reading and have a great day.