Forum Discussion

LalaLee's avatar
LalaLee
Regular Visitor
1 year ago
Solved

Determine Correlation

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.

 

Need Help
  • danextian's avatar
    danextian
    1 year ago

    Hi LalaLee 

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

     

5 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    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

     

     

    • LalaLee's avatar
      LalaLee
      Regular Visitor
      1 year ago

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

      • danextian's avatar
        danextian
        Super User
        1 year ago

        Hi LalaLee 

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

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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