Table of Contents
A correlation coefficient can be calculated in Power BI by using the built-in statistical functions. The formula for calculating the correlation coefficient is called the Pearson’s correlation coefficient, which measures the strength and direction of the linear relationship between two variables. In Power BI, this can be done by using the CORREL function, which takes in two sets of data as parameters and returns a value between -1 and 1. The closer the value is to 1, the stronger the positive correlation between the two variables, while a value closer to -1 indicates a strong negative correlation. This allows users to easily analyze and visualize the relationships between different data sets in their Power BI reports.
A correlation coefficient is a measure of the linear association between two variables.
It can take on a value between -1 and 1 where:
- -1 indicates a perfectly negative linear correlation between two variables
- 0 indicates no linear correlation between two variables
- 1 indicates a perfectly positive linear correlation between two variables
The easiest way to calculate a correlation coefficient between two columns in Power BI is to use the Quick Measure function.
The following example shows how to do so in practice.
Example: How to Calculate a Correlation Coefficient in Power BI
Suppose we have the following table named my_data in Power BI that contains information about total ad spend and total revenue for various grocery stores:
Suppose that we would like to calculate the correlation coefficient between Ad Spend and Revenue.
To do so, click the Table Tools tab and then click the Quick measure icon:
In the Quick measure panel that appears on the right side of the screen, click the dropdown arrow next to Select a calculation and then scroll down and click Correlation coefficient:
Next, choose Store for the Category, Ad Spend for the Measure X, and Revenue for the Measure Y.
Then click Add:
This will automatically generate the DAX code necessary to calculate the correlation coefficient between Ad Spend and Revenue:
To view the correlation coefficient, you can switch to the Report View and add a card visualization that displays the correlation coefficient:
We can see that the correlation coefficient between Ad Spend and Revenue is 0.56.
Related:
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: