Linear Regression in Power BI

Linear Regression is Predictive model used for finding the linear relationship between a dependent and one or more independent variables. In regression the dependent variable has to be continuous numerical (not categorical like Green, Yellow etc).

In Simple linear regression, we have one independent variable and one dependent variable. It is also known as Univariate linear regression, as there is only one independent variable.

In Multiple linear regressions, we have more than one independent variable and one dependent variable. It is also known as multivariate linear regression, as there is more than one independent variable.

Now our goal is to predict the home price by using the Simple Linear Regression.

(Name of the above table is SimpleLinearRegression)

In this table we have the prices of home based on the Area of that home. Here we have to predict the Price based on the given Area. As we are predicting the price based on the area. So, Price is the dependent variable and Area is the independent variable.

Formula for Predicting the price:-

 Y = mx + b

Here y is the Dependent variable, x is the independent variable, m is the slope means like how much Y changes when x change and b is the intercept of the Y-axis. 

Now our aim is to predict the price of a home whose area is 4200.Means 4200 is the independent variable(x).

Formula to Find m =

Now in order to find the slope(m) we need few calculated columns and measures.

Here we have added two calculated column to our dataset. These are ;

XY = SimpleLinearRegression[Area(X)]*SimpleLinearRegression[Price(Y)]

x-square = SimpleLinearRegression[Area(X)]^2

 

We have created few measures as well. These are ;

n = Countrows(SimpleLinearRegression)

XYsum = Sum(SimpleLinearRegression[XY])

Area(x)sum = sum(SimpleLinearRegression[Area(X)]) 

Price(Y)Sum = sum(SimpleLinearRegression[Price(Y)])

X-square(Sum) = Sum(SimpleLinearRegression[x-square])

 

Now create a measure in order to find m by simply putting the above existing measure.

m(slope) =

 DIVIDE (

    [n] * [XYsum] - [Area(x)sum] * [Price(Y)Sum],

    [n] * [X-square(Sum)] - [Area(x)sum] * [Area(x)sum],

    0

)

  

Formula to Find b =

Now create a measure in order to find b.

b(Intercept) =

DIVIDE (

    [Price(Y)Sum] * [X-square(Sum)] - [Area(x)sum] * [XYsum],

    [n] * [X-square(Sum)] - [Area(x)sum] * [Area(x)sum],

    0

)

 

 

Now we have values for m, x and b. So, create a measure for finding the predicted price.

Y(Predicted Price) =

[m(slope)] * 4200 + [b(Intercept)]

 

 Now you can see the predicted price in the above card visual. Which is 750924.66.