# 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 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’.

A new window will be opened, provide your server name and database name and connect your sql server by clicking on ‘Ok’.

Loaded table will be shown at the right side of the tool.

Now go to home tab and click on ‘Quick Measure’. A new quick measure window will be opened.

Click on drop down menu of ‘Select a calculation’ and go to ‘Mathematical Operations’ and click on ‘Correlation coefficient’.

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

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,

## 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.