Determine Correlation

[LalaLee]

Hi, 

I am trying to determine the correlation between property floor area (in sqm) and the sales price of the property.  I also need to be able to point out which estate where larger size property correlates strongest with higher prices

Variables that I have are:

Floor Area Per Property - SQM (num)
Sale Price Per Property - $
Estate - Text

Trans Year - Text

Would you be able to advise me how to do it?  I am totally new to Power BI, would appreciate show and tell kind of solution with screenshot to guide me.

[v-kongfanf-msft]

Hi @LalaLee ,

Did danextian, Bibiano Geraldo reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.

Best regards,

Adamk Kong

[Bibiano_Geraldo]

Hi @LalaLee ,

To determine correlation in power bi you can Create a Scatter Chart and Drag these fields into the chart:

X-axis: Floor Area Per Property.
Y-axis: Sale Price Per Property.
Legend: Estate.
This chart will show how floor area relates to sales price for each estate

To see the correlation coefficient you can reach your goal by this DAX:

Correlation =
VAR MeanX = AVERAGEX(ALL('Table'), 'Table'[Floor Area Per Property])
VAR MeanY = AVERAGEX(ALL('Table'), 'Table'[Sale Price Per Property])
VAR Numerator =
SUMX(
ALL('Table'),
('Table'[Floor Area Per Property] - MeanX) *
('Table'[Sale Price Per Property] - MeanY)
)
VAR Denominator =
SQRT(
SUMX(ALL('Table'),
POWER('Table'[Floor Area Per Property] - MeanX, 2)
) *
SUMX(ALL('Table'),
POWER('Table'[Sale Price Per Property] - MeanY, 2)
)
)
RETURN Numerator / Denominator

To Analyze Correlation Per Estate Create another measure by this DAX:

Correlation Per Estate =
VAR MeanX = AVERAGEX(ALL('Table'), 'Table'[Floor Area Per Property])
VAR MeanY = AVERAGEX(ALL('Table'), 'Table'[Sale Price Per Property])
VAR Numerator =
SUMX(
FILTER(ALL('Table'), 'Table'[Estate] = SELECTEDVALUE('Table'[Estate])),
('Table'[Floor Area Per Property] - MeanX) *
('Table'[Sale Price Per Property] - MeanY)
)
VAR Denominator =
SQRT(
SUMX(FILTER(ALL('Table'), 'Table'[Estate] = SELECTEDVALUE('Table'[Estate])),
POWER('Table'[Floor Area Per Property] - MeanX, 2)
) *
SUMX(FILTER(ALL('Table'), 'Table'[Estate] = SELECTEDVALUE('Table'[Estate])),
POWER('Table'[Sale Price Per Property] - MeanY, 2)
)
)
RETURN Numerator / Denominator

Add a table visual to display Estate and Correlation Per Estate

Add a slicer for Estate to filter data and focus on specific correlations

[danextian]

Hi @LalaLee 

I am not really familiar with this but I followed the tutorial on this blog post -https://datakuity.com/2021/10/29/correlation-coefficient-in-power-bi-using-dax/ for the attached sample pbix.

[LalaLee]

Apologise, I am totally new to this.  If possible, would appreciate show and tell solution, with screenshots.

[danextian · Accepted solution]

Hi @LalaLee 

There  is a sample pbix attached in my previous post. You can download it and have a look at the calculations.