How to Predict Outcomes with Simple Linear Regression in Power BI

In the realm of statistics, simple linear regression provides a quantifiable method to define the relationship between a single predictor variable and a single response variable.

The following guide details a step-by-step process for implementing and utilizing a simple linear regression model directly within your Power BI environment, using the powerful built-in functions.

Step 1: Preparing and Loading the Dataset

The initial requirement for any analysis in Power BI is to properly load the relevant data. For this example, we will import a dataset named my_data. This table contains critical business metrics: the total Ad Spend (our predictor variable) and the subsequent total Revenue generated (our response variable) across several retail operations. This dataset will form the foundation for modeling the relationship between marketing expenditure and sales performance.

Step 2: Implementing the Linear Regression Model Using DAX

To formally calculate the simple linear regression model, we will designate Ad Spend as our predictor variable (X) and Revenue as our response variable (Y). The goal is to determine the coefficients, or parameters, that best define the linear relationship, which follows the standard mathematical form:

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

Within Power BI, we employ the powerful DAX LINEST function to achieve this. Navigate to the Table tools tab in the ribbon and select the New table icon. This action initiates the process of creating a calculated table that will store the regression statistics.

Next, input the following formula into the formula bar. The LINEST function requires the response variable (Revenue) first, followed by the predictor variable (Ad Spend), both referencing our my_data table:

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

Executing this DAX calculation will generate a new calculated table named Model. This table contains various statistical measures summarizing the fitted simple linear regression model, including the intercept, slope, and R-squared values, which are essential for model assessment.

Note: For detailed syntax and interpretation of the array of statistics returned, you can consult the official documentation for the LINEST function in DAX.

From this output, the two most critical values are Slope1 (which corresponds to β₁) and Intercept (which corresponds to β₀). These coefficients allow us to formally construct the specific fitted regression equation for predicting revenue based on ad spend:

Revenue = 8.67444 + 1.10958*(Ad Spend)

• The Intercept (8.67444) signifies that if a store spends zero dollars on ads (Ad Spend = 0), the predicted baseline revenue is $8.67444.
• The Slope1 (1.10958) indicates that for every one additional dollar spent on advertising, the predicted revenue increases by an average of $1.10958, showcasing a positive correlation between the variables.

Step 3: Visualizing and Applying Dynamic Predictions

After fitting the model, a crucial step is visualization. We can start by inserting a standard scatter chart in the Report view, mapping Ad Spend to the x-axis and Revenue to the y-axis. This visualization instantly provides a graphical representation of the correlation between the two variables, often supplemented by adding a standard trend line to visually confirm the calculated slope.

To make the prediction model interactive, we will now implement a dynamic input control. This setup will allow users to adjust the hypothetical Ad Spend value using a slider bar and immediately view the corresponding Predicted Revenue calculated by our regression equation.

To create this input control, go to the Modeling tab in Power BI Desktop. Click the New parameter icon, and then select Numeric range from the subsequent dropdown menu. This action initiates the creation of a calculated table and a slicer visual simultaneously.

In the configuration window, name the parameter Ad Spend. We will set the range reflective of our data’s context: Minimum value to 0 and Maximum value to 20. Crucially, ensure the box next to Add slicer to this page is checked, then confirm by clicking OK. This action seamlessly integrates the control element into your report view.

A new slicer visualization will appear on the canvas, allowing users to select any numerical value for Ad Spend between 0 and 20. This selected value will serve as the input for our prediction measure in the next step.

Step 4: Defining the Predicted Revenue Measure

Now, we must create a measure that dynamically calculates the predicted revenue using the linear equation derived in Step 2, incorporating the value selected by the new slider. Switch to the Table view or Data view, navigate back to the Table tools tab, and click New measure.

Input the following DAX formula into the measure bar. This formula retrieves the calculated Intercept (β₀) and Slope1 (β₁) from the Model table using SELECTCOLUMNS, and multiplies the slope by the current value of the Ad Spend Value parameter:

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

This newly created measure, Predicted Revenue, is the core of our dynamic prediction engine. It ensures that the calculation is always up-to-date based on the user’s input from the slicer, effectively implementing the fitted regression equation: Y = β₀ + β₁X.

Step 5: Visualization and Model Verification

To display the output of our new measure clearly, switch back to the Report view. Insert a Card visualization onto the canvas and assign the Predicted Revenue measure as the field. This card will now instantly reflect the predicted outcome corresponding to the Ad Spend value selected via the slicer.

The Card visualization serves as the output display, showcasing the calculated prediction based on the input variable. This dynamic display provides immediate feedback, transforming a static statistical model into an interactive business tool for forecasting.

For verification purposes, let’s test a specific input. If the user sets the Ad Spend slider value to 10, the Predicted Revenue displayed on the card should be approximately 19.77. We can manually confirm this result by substituting the value into our original fitted regression equation:

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

This confirms the accuracy of the DAX measure. Users are now encouraged to manipulate the value on the Ad Spend slider to instantaneously observe how various levels of expenditure impact the Predicted Revenue value, providing actionable insights for budget planning.

Conclusion and Further Power BI Analysis

By utilizing the LINEST function in DAX, we successfully transitioned from raw data to a fully implemented and dynamic simple linear regression model within Power BI. This process demonstrates how to move beyond basic visualizations to perform rigorous statistical analysis and build interactive forecasting tools. This foundational method can be extended to handle more complex statistical models, providing significant analytical depth to your reports.

Explore these related tutorials to enhance your skills in performing other advanced data analysis and common tasks within the Power BI environment: