Help with Statistics: Linear Regression, Correlation, Confidence Interval

[Anonymous]

Hello everyone,

I have two columns of data that are somewhat correlated. My task here is to show that correlation, and to use it to predict future outcomes (e.g given X, what is y) as well as the margin of error.

First I plotted X and Y on the scatter visual, and added a trendline. Next I used the Correlation Coefficient quick measure which gave me -0.680.

Next I used this linear regression, so that a report user could choose X and see what the estimated Y value would be based on historical data.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/td-p/247439

The regression model mirrors the trend line that Power BI produced.

The last piece I want to include is a margin of error, or some degree of confidence. Here's where I need help (it's been a long time since I took statistics).

At first I thought I could take the standard deviation of the error (abs(EstimatedY-ActualY) for all rows, multiply by 1.96 for 95% confidence and add it to the mean error.  Say it's 0.06 for the sake of illustration.  Am I showing that 95% of the errors land within +- 0.06 of the trendline?

[v-juanli-msft]

Hi @Anonymous

Does the margin of error which calculated as you said satisfy your requirement?

What you are seeking for is to show the value of “margin of error” around a trendline?

Best Regards

Maggie

[Anonymous]

Yes, that's exactly what i am trying to calculate - the margin of error/confidence interval around the trendline.

Determining the margin of error for set of values (one variable) is simple...  what I don't understand is how to do for a trendline becauses there are two correlated values.

Any stats gurus who know how this can be done, please help 🙂