Section
View the tutorial in the Power BI Dashboard or keep scrolling for text!
Wait… What is Linear Regression?
Linear Regression is a statistical model applied to businesses to help forecast events based on historical trend analysis. Simple Linear regression uses one variable, called the independent variable. The independent variable predicts the outcome of another variable called the dependent variable.
A Linear Regression Model is created by fitting a trend line to a dataset where a linear relationship already exists.
This trend line has the equation of y = mx + b and is used to make estimates.
(Note: To successfully implement Linear Regression on a dataset, you must follow the four assumptions of simple Linear Regression. Statistical tests can be performed to check the validity of the model, however, this process is beyond the scope of this tutorial.)
Tutorial
Consider this Scatterplot visual. Illustrated is the relationship between Years of Experience and Salary at a fictional company. By fitting a trend line to the Scatterplot, we can see that the more years of experience an employee has, the more they will get paid. Our y data points represent Salary in thousands, and the x data points represent Years of Experience.
Click Here to Follow the Tutorial in Power BI
According to the data above, if an employee has 15 years of experience, how much will they be paid? What about 20 years of experience? 30 years?!
Let’s try this out!
Steps we need to take:
- Create calculated columns and measures for the components of the equation of a line
- Create a What-if parameter for the x-values
- Create the measure of the equation of a line and insert a card visual with the measure
If you would like to follow along, this tutorial is demonstrated using the Salary_data dataset from Kaggle.com.
Step 1: Create Calculated Columns and Measures
The formula for the equation of a line is y = mx + b.
Where:
y = How far up the y axis
m = Slope (Change in y divided by change in x) =
x = How far along the x axis
b = b-intercept (The value of y when x = 0) =
After finding out m and b with some calculations, we can input any data point for x and the output will be y.
We are going to create this formula using DAX calculated columns and measures.
These are the Calculated Columns we need to make and their DAX syntax:
| Math Formula | DAX Formula |
| x² | xsq = Salary_Data[Years of Experience]^2 |
| xy | xy = Salary_Data[Years of Experience]*Salary_Data[Salary] |
These are the Measures we need to make and their DAX syntax:
| Math Formula | DAX Formula |
| n | n = COUNTROWS(Salary_Data) |
| ∑xy | xysum = SUM(Salary_Data[xy]) |
| ∑x | xsum = SUM(Salary_Data[Years of Experience]) |
| ∑y | ysum = SUM(Salary_Data[Salary]) |
| ∑x² | xsqrsum = SUM(Salary_Data[xsq]) |
| (n(∑xy)-(∑x)(∑y))/(n(∑x²)-(∑x)²) | m (Slope) = DIVIDE( [n]*[xysum]-[Xsum]*[Ysum], [n]*[xsqrsum]-[Xsum]^2, 0 ) |
| ((∑y)(∑x^2)-(∑x)(∑xy))/(n(∑x²)-(∑x)²) | b (Intercept) = DIVIDE( [ysum]*[xsqrsum]-[Xsum]*[xysum], [n]*[xsqrsum]-[Xsum]^2, 0 ) |
Step 2: Setting up a What-if parameter
Now that we have performed our calculations, for any number we insert into x, the formula will predict y.
To demonstrate this, we will be using a What-if parameter with a slicer.
A What-if parameter allows you to create an interactive slicer with a variable to visualize and quantify another value in your report.
After clicking the What-if parameter on the modeling ribbon, we can input these values:
Name: x (Years of Experience)
Data type: Whole Number
Minimum: 0
Maximum: 30
Increment: 1
Add slicer to this page: Yes
Once we press ‘OK’, the What-if parameter will have generated a series of x values and a slicer to use for our What-if scenarios.
Step 3: Complete the measure for the equation of a line and visualize
Finally, we create the Predicted Salary measure that contains the complete formula for the regression equation of a line y = mx + b, using the components we have created previously.
| Math Formula | DAX Formula |
| y = mx + b | Predicted Salary = ([m (Slope)]* ‘x (Years of Experience)'[x (Years of Experience)Value]+ [b (Intercept)] ) |
We can now insert a card visual in the report and select the measure Predicted Salary for the field.
We can test the calculation by toggling the slicer left and right. This action will interact with the Card visual and represent the predicted salary for the selected Years of Experience.
Create awesome visualizations!
There are many ways to visualize the prediction that we have set up using Linear Regression. A toggle and a card provide enough information, but we can also integrate that calculation into a chart to visualize the existing data and the predicted value.
Now you now have the power to make predictions!
Conclusion
Linear Regression helps forecast future events by fitting a trend line to the model and using the equation of a line to predict our values. By following the steps in this tutorial, you can implement Linear Regression on a valid dataset and make estimations on future values. Try this tutorial out with a public dataset and share your findings!
Resources
If you would like to learn more statistical functions in DAX, check out Stephen Zielke’s blog post!
This tutorial was in reference to this YouTube video: DAX Fridays! #135: Linear Regression in Power BI.
The dataset used for this tutorial is called Salary_Data found a Kaggle.com.
The formulas used for Linear regression were referenced from Linear Regression: Simple Steps, Video. Find Equation, Coefficient, Slope – Statistics How To.
Stay Up to Date with Our Latest Blogs
Subscribe to our mailing list to be notified when a new article is released.
26 Comments
Very helpful article! Would you be able to point to any resources that could help accomplish something similar with multiple independent variables?
Hi Nick,
Good news! We just published Diana’s article on Multiple Linear Regression here.
Happy Reading!
Ha ha. Exceptional timing! Thanks for the link.
Hi – this is an amazing demo – thanks for sharing this.
I really liked your final visual – but I can’t work out how you linked the trend line to end at the slicer value. Could you share how you achieved this please?
Hello Dan,
I followed up with Diana and this was her response:
I’m happy you enjoyed the article!
For the last visual in the Result page, there was a table and two DAX measures created for the values. The table was a table of years that looked like this:
This table was used for the axis of the visual. The first DAX measure uses a variable and says return the predicted salary formula for the selected value. The second DAX measure is the predicted salary formula, modified (using TREATAS) to use our newly created table instead of the original Years of experience column.
I hope this helps!
Hello Dan!
Hello Jan-Paul! Check out the steps I put in the Dashboard on how to make the visual in the Results page!
Hope this helps!
Simply awesome Diana ! This is the best explanation I have ever come across on this topic.. Thank you so much. Keep sharing
This is awesome and thank you very much for your post. However I am still confused with the last visual (is it scatter chart?). Could you please write down the 2 measures and take the screenshot of the visualization configuration?.
Thank you very much!
Thank you Quan!
I will be posting an updated version of this blog dashboard with an info page showing how the visual in the ‘Result’ tab was made.
Stay Tuned!
Hello Quan! Check out the steps I put in the Dashboard on how to make the visual in the Results page! Dax calculations included!
Hope this helps!
Great article, very helpful. Thank you for taking the time to share
Looking forward to the dax used on the results page!
Hello Jan-Paul!
My apologies to you and everyone that it is taking so long for me to post that page!
I will get on it soon!
Thank you!
Any idea when this will be published? I’d love to understand how that visual works. Many thanks!
I am so sorry to you Rebecca and everyone who has been waiting!
I have been crazy busy at work, but I am hoping to have it published by the weekend, if not next week!
Thank you for everyone’s patience 🙂
Thanks for your response Diana, much appreciated. This is a hugely helpful article and demonstration, thanks for your efforts.
Hello Rebecca! Check out the steps I put in the Dashboard on how to make the visual in the Results page!
Hope this helps!
Hello Jan-Paul! Check out the steps I put in the Dashboard on how to make the visual in the Results page! Dax calculations included!
Hope this helps!
Hi There, How about formula for Linear Regressoin Multi Variable?
Hello Nhan!
Check out my second blog post for Multiple Linear Regression in Power BI here! = https://iterationinsights.com/article/multiple-linear-regression-in-power-bi-with-r-script/#:~:text=Multiple%20Linear%20regression%20is%20a,extension%20of%20simple%20linear%20regression.
Nice post. I waas checking constanly this bkog and I am impressed!
Extremely useful inf particularly the last part 🙂 I care for such informaion a
lot. I was seeking this certain info forr a lpng time.
Thank you and best off luck.
Super helpful!
However, how do I sum an X-axis that are dates? I have date codes if it helps!
Hello Thais,
Just to get a little more clarity on your question, are you trying to predict the date instead of the number of years?
Thanks for reading!
Great article! However, I’m still trying to figure out the steps for the last visual. Could you please point in the right direction on how to accomplish this? (Chart type, measures/data model, screenshots etc.). Thanks!
Hello Hassan, if you click through the Power BI dashboard at the beginning of the article, on the ‘Result’ page there is button to click called ‘How I made this visual’ which will give you step by step guide on how to accomplish this.
Thanks so much for Reading!
Hi. Really love the article and explanations. Thank you.
Have implemented and seem to have done it right.
Finding a significant challenge with the last chart. Id really like to see the final PBIX so I can pull it apart to understand how you presented that data as a Scatter Chart because I can’t get anything to work. Is there any chance you can find a way to share it?