Power Bi : Build a simple Linear Regression Equation in DAX to Predict a Goal
HTML-код
- Опубликовано: 1 авг 2024
- **UPDATE**: We have reached 850 SUBSCRIBERS as of 3/17/2022. What date did the model predict we would hit this goal - • Power Bi : Evaluating ...
We have reached 500 subscribers!
In this video I will teach you how to build a simple linear regression model to predict the exact day when the channel will hit 1,000 subscribers.
0:00 Start
0:28 RUclips Dataset
2:01 Calculate Running Total
6:24 The Regression Equation
8:37 Regression in DAX
16:25 Graphing Regression Line
17:10 The Slope
24:57 Expected Goal Date
29:12 Regression past Current Day
35:16 Thank You!
I apologize about the audio quality during the first few minutes. Issues with my MIC.
Helpful blog: xxlbi.com/blog/simple-linear-...
DAX FORMULAS -
RUNNING TOTAL:
TimeFrameSubscribers =
CALCULATE(Sum(Subscribers[Subscribers]),
FILTER(ALLSELECTED(Subscribers),
Subscribers[Date] <= Max(Subscribers[Date]))
)
REGRESSION EQUATION (DAX):
RegressionLine =
var data =
FILTER(
SELECTCOLUMNS(ALLSELECTED(Subscribers),
"x_values",Subscribers[Date],
"y_values",[TimeFrameSubscribers]
),
AND(
NOT(ISBLANK([x_values])),
NOT(ISBLANK([y_values]))
)
)
--Variables to Solve for:
var y_sum = SUMX(data,[y_values])
var x_sum = SUMX(data,[x_values])
var x2 = SUMX(data,[x_values]^2)
var xy = SUMX(data,[x_values]*[y_values])
var row_count = COUNTROWS(data)
--Solve for formula
var Intercept = (y_sum * x2 - x_sum * xy)/ (row_count * x2-(x_sum) ^2)
var Slope = (row_count * xy - x_sum * y_sum)/( row_count * x2-(x_sum) ^2)
--Regression
var Regression = SUMX(Subscribers, Slope * Subscribers[Date] + Intercept)
return Regression 
Наука
Having been learning PBI for the last few years and having watched lots and lots of videos - this is one of the best - absolutely awesome. I've already adapted it for new insights in my organisation. I'm subscribed
Hey Jamie. Thanks brother. Glad it helped you out. Sharing knowledge is how we all grow and learn. Appreciate the sub.
The breakdown of the linear regression is gold!!! Thank you very much for sharing this!!
Hey Raul! I appreciate the comment. Thank you. I am glad you found it useful.
You are absolutely brilliant!! I'm subscribed, l guess because of this video you are reaching the 1k way earlier than predicted.
Thank you thank you 🙏. We are on our way to 1K. Baby steps.
Thank you! Keep on the good work!
That's really good. When i did this for my data I had to summarize the raw data as I had multiple entries per day.
Incredible what you do... We close a difficult deal just by applying what I get from here. Fantastic job and really many thanks to share with us. Not just subscriber, a fan from now on!
Oswaldo, I totally missed your comment. That is amazing! I am glad the content was able to help you. That's what it is all about, helping each other out. I provided a video update so you can see how close the model is to where my channel is today. The link is in the description. Check it out.
Good info! Great stuff!
you taught me EXACTLY what I needed for my job! (by the way, in october, when I first watched this video you were at 1,3k subscribers, so your trendline must be correct, now I'm another sub!)
Looks on you are on track for 1000 subs, good video!!
Hey Warren, haha yeah the regression told me sometime in June, so we'll see. I think it will be closer to July but it was a fun experiment
That's awesome, You're a genius, guy, I'm your fan from now on 👍
But we will be thankful if you make another tutorial for multiple regression using two or three variables 🙏
Salah, thanks for the support. Yes, I am actually working on putting together a multiple variable regression tutorial. Stay tuned.
Thanks!
Nice
Hi Victor! This is awesome! I am so happy to find you. I am curious on when did you hit 1000 subscriber? :)
Holy shit, this is what my boss wanted and I managed to do this because the dataset is almost similar. lmao thank you!!
Thanks for the informational tutorial. Do you know how can we show the values in %?
Like in terms of trend how much is it up/down
Congratulations, you hit a thousand before the predicted Date.
Hey! yeah I ended up reaching the goal on June 9th!. It is crazy how accurately close it was.
hi, thank you for your tutorial. can you explain how, to sum up of x variable? because x is the date. not number.
I have two columns, X and Y, and want to make a simple linear regression to predict Y based on X. I need to be able to apply a filter to choose selected rows based on a value in a third column, Z,. Will it be possible to do this using this method?
It's June 15 2022 and you have 1k subscribers :)
Hey Marcin! I ended up reaching the goal on June 9th. Crazy how accurately close it was. It was a fun exercise.
I have daily sales data but I want the linear regression by month year this is not. Giving correct result could you please help .thanks
Comprehensive solution how to create regression for beginners
great，可以出个指数平滑法预测视频吗
I will look into Exponential Smoothing and see what I can build. It would most likely be simple exponential smoothing. Anything else may require Python or R to display.
A little help. Very frustrated right now.
I keep getting this error.
The syntax for ')' is incorrect. (DAX(var data=SELCTCOLUMNS(ALLSELECTED(DataSource), "x_values",DataSource[CloseDate], "y_values",[SalesVolume]))).
This is what I have typed into the DAX Editor
1 RegressionLine =
2
3 var data =
4
5 SELECTCOLUMNS(ALLSELECTED(DataSource),
6 "x_values",DataSource[Close Date],
7 "y_values",[SalesVolume]
8 )
I have verified there is no blank data.
Any help is appreciated.
I have encountered the same problem. Did you manage to solve it?
idk what I did by my regression line is flat. Also, I am using a really bad data set so I think thats the reason.
EasyPeasey, my email is on the "About" section of the channel. You can send my a csv or excel file of your dataset and I can see what might be going on.
@@DapperDash I believe I figured it out. I needed to make a measure versus using quick measures. But now m regression line goes up and down versus a line. I think that is technically fine tho.
The analisis was correct, you are 1000 subscribers by jun!! im subscribe now!!
It was kind of interesting, I hit the goal about a day early from the video prediction. For a simple linear regression, it was scary how accurate it became.
Muito legal! Sou do Brasil e produzo conteúdos também!