How to Do Linear Regression in Power BI

Linear regression might sound like an intimidating term from a statistics class, but it’s actually a straightforward and powerful way to find relationships in your data. It helps you answer questions like, "If we increase our ad spend by 20%, what kind of sales lift can we expect?" This article will walk you through exactly how to perform a linear regression analysis in Power BI, turning your historical data into a predictive tool.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Exactly is Linear Regression?

In simple terms, linear regression is a method for modeling the relationship between two numerical variables. The goal is to draw a straight line - a "trend line" - through your data points that best summarizes the relationship.

To keep things practical, let's use a common business scenario: analyzing the connection between monthly marketing spend and website traffic.

• Your independent variable is the thing you control or change. In this case, it’s the marketing spend. This goes on the X-axis (the horizontal one).
• Your dependent variable is the thing you're measuring the effect on. Here, it’s the website traffic. This goes on the Y-axis (the vertical one).

Linear regression finds the "line of best fit" through these data points. Once you have this line, you can use it to predict future traffic based on a planned marketing budget. It helps quantify the gut feeling that "more ad spend leads to more traffic" into a concrete, measurable formula.

Getting Your Data Ready for Analysis

Before you can build a model, you need to set up your data correctly in Power BI. The foundation of a visual regression analysis is a scatter chart, which requires at least two columns of numerical data that you believe are related.

1. Ensure You Have the Right Data

Your dataset needs to be clean and simple. For our example, let’s assume you have a table named Marketing Performance with at least two columns: Ad Spend and Sessions. Make sure both columns are formatted as numbers (like whole number or decimal number) in Power BI’s data view.

Your data might look something like this:

• Month 1: Ad Spend: $1,200, Sessions: 15,000
• Month 2: Ad Spend: $1,500, Sessions: 18,500
• Month 3: Ad Spend: $1,100, Sessions: 14,000
• Month 4: Ad Spend: $2,000, Sessions: 23,000
• ...and so on.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

2. Create a Scatter Chart Visual

A scatter chart is the perfect visual to see the relationship between two variables. Each point on the chart represents a pair of values (e.g., the Ad Spend and Sessions for a specific month). If the points seem to form a general line sloping upwards, you likely have a positive correlation worth investigating.

Don't worry, creating one is simple, and we'll cover the exact steps in the next section.

Step-by-Step Guide: Visual Linear Regression in Power BI

Once your data is ready, you can perform the visual part of the analysis in just a few clicks. This gives you an immediate picture of the relationship you're investigating.

Step 1: Build the Scatter Chart

1. Go to the Report view in Power BI.
2. In the Visualizations pane on the right, click on the Scatter chart icon (it looks like a cloud of dots).
3. An empty visual will appear on your canvas. With the visual selected, drag your independent variable (Ad Spend) from the Fields pane to the X-Axis field in the Visualizations pane.
4. Next, drag your dependent variable (Sessions) to the Y-Axis field.

You should now see a scatter plot where each point represents a month, showing the relationship between how much you spent and the traffic you received. By default, Power BI might summarize your data. To fix this, find the Ad Spend and Sessions fields in the X and Y axis buckets and click the dropdown arrow. Make sure Don't summarize is selected so you see a dot for every row in your data table.

Step 2: Add the Trend Line

The trend line is the visual representation of your linear regression model. Power BI makes this incredibly easy to add.

1. With your scatter chart visual selected, click on the magnifying glass icon in the Visualizations pane. This opens the Analytics pane.
2. Find the Trend line option and click Add.

A single straight line will immediately appear, cutting through your data points. This is your "line of best fit." It visually indicates the overall trend. If it slopes upwards from left to right, you have a positive correlation. If it slopes downwards, it's a negative correlation.

This visual is great for presentations, but to make actual predictions, you need the mathematical formula behind that line.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Unlocking the Formula with DAX

The visual trend line is a great start, but the real power comes from extracting the formula that defines it. Every straight line can be described by the formula: y = mx + b

• y is your dependent variable (Sessions).
• x is your independent variable (Ad Spend).
• m is the slope of the line. It tells you how much y changes for every one-unit increase in x. (e.g., "for every extra dollar in ad spend, we get an extra 10 sessions.")
• b is the intercept. It's the value of y when x is zero. (e.g., "if we spent $0 on ads, we'd still get 5,000 baseline sessions.")

While Power BI doesn’t display this formula on the visual directly, we can calculate the slope and intercept using DAX measures.

Calculating the Slope

The slope tells you the rate of change. Here’s the DAX formula to calculate it. Click New measure in the toolbar and enter the following:

Slope = 
VAR DataSummary =
    SUMMARIZE(
        'Marketing Performance',
        'Marketing Performance'[Ad Spend],
        "Sessions", SUM('Marketing Performance'[Sessions])
    )
VAR AvgX = AVERAGEX(DataSummary, [Ad Spend])
VAR AvgY = AVERAGEX(DataSummary, [Sessions])
VAR Numerator =
    SUMX(
        DataSummary,
        ([Ad Spend] - AvgX) * ([Sessions] - AvgY)
    )
VAR Denominator =
    SUMX(DataSummary, ([Ad Spend] - AvgX) ^ 2)
RETURN
    DIVIDE(Numerator, Denominator)

Calculating the Intercept

The intercept tells you your starting point. To calculate it, create another new measure. This formula is a little simpler because it can reuse the Slope measure you just created.

Intercept = 
VAR DataSummary =
    SUMMARIZE(
        'Marketing Performance',
        'Marketing Performance'[Ad Spend],
        "Sessions", SUM('Marketing Performance'[Sessions])
    )
VAR AvgX = AVERAGEX(DataSummary, [Ad Spend])
VAR AvgY = AVERAGEX(DataSummary, [Sessions])
RETURN
    AvgY - ([Slope] * AvgX)

Now, you can display these measures on your dashboard using Card visuals for a clear view of your model's components.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Putting It All Together: Making Predictions

With your slope and intercept calculated, predicting future outcomes is simple. You can create a third DAX measure to calculate a predicted outcome based on a hypothetical input.

Imagine you have a "What-If" parameter set up for a future ad spend number. Let's call it Future Ad Spend Value. You can create a measure like this:

Predicted Sessions = 
([Intercept]) + ([Slope] * [Future Ad Spend Value])

Now, if you use a slicer for your "Future Ad Spend", this Predicted Sessions card will update in real-time. If you slide the ad spend to $2,500, the measure will instantly calculate the number of sessions your model predicts you'll receive.

Important Considerations

Before you start making major business decisions based on your model, keep these points in mind:

• Correlation is Not Causation: Linear regression shows that two variables move together, but it doesn't prove that one causes the other. Another hidden factor could be influencing both. Always apply business context to your findings.
• Data Quality Matters: Your model is only as good as the data you feed it. Outliers or incorrect data can significantly skew your trend line. Clean your data first!
• Don't Predict Too Far Out: Your model is most reliable within the range of your actual data. If your ad spend has only ever been between $1,000 and $2,000, using the model to predict traffic for a $10,000 ad spend could be highly inaccurate.

Final Thoughts

By moving beyond a simple visual, you can turn a basic Power BI scatter plot into a practical forecasting tool. Combining the trend line with a few straightforward DAX measures gives you the power to quantify relationships in your data and make informed, data-driven predictions about the future.

While this manual process in Power BI is effective, we know that fiddling with DAX formulas isn’t for everyone. With Graphed, we created a way to get these kinds of insights just by asking for them. After connecting your data sources, you could simply ask, "What is the relationship between ad spend and sessions?" and get an instant analysis and visualization. Our goal is to handle the complex calculations for you, so you can skip straight to understanding what your data means for your business.