How to Perform Simple Linear Regression in Power BI?

---

In statistics, is a technique we can use to quantify the relationship between a predictor variable and a .

The following step-by-step example shows how to perform simple linear regression in Power BI.

Step 1: Load the Data

First, we will load the following table named my_data that contains information about total ad spend and total revenue generated by various retail stores:

Step 2: Fit the Linear Regression Model

Suppose that we would like to fit a simple linear regression model, using Ad Spend as the predictor variable and Revenue as the response variable:

Response = β₀ + β₁*(Ad Spend)

To do so, click the Table tools tab and then click the New table icon:

Then type the following formula into the formula bar:

Model = LINEST(
	'my_data'[Revenue],
	'my_data'[Ad Spend]
)

This will fit a simple linear regression model and create a table with various statistics that summarize the model:

Note: You can find the complete documentation for the LINEST function in DAX .

The most important values in the output are the Slope1 and Intercept values, which we can use to write the fitted regression equation:

Revenue = 8.67444 + 1.10958*(Ad Spend)

• If a store spends zero dollars on ads then their predicted revenue is $8.67444.
• For each additional dollar that a store spends on ads, their predicted revenue increases by an average of $1.10958.

Step 3: Use the Regression Model to Make Predictions

Next, we can insert the following scatter chart with Ad Spend on the x-axis and Revenue on the y-axis along with a trendline to visualize the relationship between the two variables:

Next, we will create a slider bar where we can change the value of Ad Spend and then see the predicted value for Revenue.

To do so, click the Modeling tab and then click the New parameter icon, then click Numeric range from the dropdown menu:

In the new window that appears, we’ll type Ad Spend as the Name, set the Minimum value to 0 and the Maximum to 20, check the box next to Add slicer to this page, then click OK:

This will insert a new slicer that we can slide from 0 to 20:

Next, switch back to the Table view. Then click the Table tools tab and then click New measure:

Then type the following formula into the formular bar:

Predicted Revenue = SELECTCOLUMNS('Model', [Intercept]) + SELECTCOLUMNS('Model', [Slope1])*'Ad Spend'[Ad Spend Value]

This will create a new measure named Predicted Revenue that uses the slope and intercept of the regression model along with the value in the new slider bar to calculate the predicted revenue:

Switch back to the Report view and insert a Card visualization. Then use the new measure Predicted Revenue as the field for the card:

The card visualization displays the predicted revenue based on the value for Ad Spend in the slider.

For example, when Ad Spend is equal to 10, Revenue is predicted to be 19.77.

We can verify this is correct by manually plugging these values into the fitted regression equation we found earlier:

• Revenue = 8.67444 + 1.10958*(Ad Spend)
• Revenue = 8.67444 + 1.10958*(10)
• Revenue = 19.77

Feel free to move the value on the slider to see how various values for Ad Spend affect the Predicted Revenue value.

Additional Resources

The following tutorials explain how to perform other common tasks in Power BI: