topic Re: Simple Linear Regression with DAX in Desktop
https://community.powerbi.com/t5/Desktop/Simple-Linear-Regression-with-DAX/m-p/13056#M3241
<P>Thx for the example!</P><P> </P><P>I was able to use your example to do a simple lineair regression forecast using a calender table. For "X" I used the date numbers (E.g.: 20150101), which is an extra column 'DateNumber' in my date table that displays the date as a number. Because I need future dates in my calender table to plot the forecast against, I had to add an extra FILTER to exclude future years from the sample being used in the calculation.</P><P> </P><PRE>Count:=COUNTAX(FILTER(ALL('Calender'); YEAR(Calender[DATE]) < YEAR(NOW()));'Calender'[DateNumber])</PRE>Tue, 22 Dec 2015 23:44:17 GMTRandy2015-12-22T23:44:17ZSimple Linear Regression with DAX
https://community.powerbi.com/t5/Desktop/Simple-Linear-Regression-with-DAX/m-p/9272#M1736
<P>OK, related to forecasting, I went ahead and followed my own advice and built out a forecasting model in DAX using simple linear regression. Probably can be improved but here is how I did it:</P><P> </P><P>CSV Files:</P><P>regression.csv</P><P>X,Y<BR />60,3.1<BR />61,3.6<BR />62,3.8<BR />63,4<BR />65,4.1</P><P> </P><P>estimation.csv:</P><P>X<BR />64<BR />75<BR />58</P><P> </P><P>In regression table, create the following columns:</P><P>X*Y = [X]*[Y]</P><P>X*X = [X]*[X]</P><P> </P><P>In regression table, create the following measures:</P><P>Count = COUNTAX(ALL('regression'),[X])</P><P>SumX = SUMX(ALL('regression'),[X])</P><P>SumY = SUMX(ALL('regression'),[Y])</P><P>SumX*Y = SUMX(ALL('regression'),[X*Y])</P><P>SumX*X = SUMX(ALL('regression'),[X*X])</P><P>Slope = ([Count]*[SumX*Y] - [SumX]*[SumY]) / ([Count]*[SumX*X] - [SumX]*[SumX])</P><P>Intercept = ([SumY] - [Slope]*[SumX]) / [Count]</P><P> </P><P>In regression and estimation tables create the following column:</P><P>Estimate = [Intercept] + [Slope]*[X]</P><P> </P><P>You can now plot your original values and the linear regression estimation values as well as plot your X values for estimation and the linear regression estimates.</P>Mon, 02 Nov 2015 14:21:09 GMThttps://community.powerbi.com/t5/Desktop/Simple-Linear-Regression-with-DAX/m-p/9272#M1736Greg_Deckler2015-11-02T14:21:09ZRe: Simple Linear Regression with DAX
https://community.powerbi.com/t5/Desktop/Simple-Linear-Regression-with-DAX/m-p/9328#M1759
<P>Cool, thanks for the step-by-step.</P><P> </P><P>With using "ALL" in the measures, does that limit filtering based on a date range for the time intelligence functions? I.E. - projecting next quarter based on only last quarter? Or is that intentional based on wanting the max available time horizon for analysis?</P>Mon, 02 Nov 2015 20:58:05 GMThttps://community.powerbi.com/t5/Desktop/Simple-Linear-Regression-with-DAX/m-p/9328#M1759ALeef2015-11-02T20:58:05ZRe: Simple Linear Regression with DAX
https://community.powerbi.com/t5/Desktop/Simple-Linear-Regression-with-DAX/m-p/9352#M1770
<P>In theory, I believe that you could use any filtering mechanism you wanted, you would not have to use ALL. The issue is that if you don't use some sort of aggregation function like SUMX and try to just use SUM, the process will not work because when you place those measures into a column formula, they become filtered by the row they are in and it jacks everything up (technical term). So, you HAVE to use some sort of FILTER. I used ALL as an example since I theorized that would be a common requirement to do a linear regression using all available data. </P><P> </P><P>It is a good thing to call out, I originally tried to just use COUNT and SUM in my measures and was getting a lot of "Not a number" in my Estimate column until I realized that I needed to use COUNTAX and SUMX instead so it is definitely worth noting. Thanks for calling it out.</P><P> </P><P>I think that a logical next step would be to come up with a Simple Rolling Linear Regression Pattern, that would be pretty spiffy. Probably could do that using a combination on the rolling aggregations patterns on daxpatterns.com and this pattern here.</P>Tue, 03 Nov 2015 03:20:07 GMThttps://community.powerbi.com/t5/Desktop/Simple-Linear-Regression-with-DAX/m-p/9352#M1770Greg_Deckler2015-11-03T03:20:07ZRe: Simple Linear Regression with DAX
https://community.powerbi.com/t5/Desktop/Simple-Linear-Regression-with-DAX/m-p/9363#M1775
<P>Or we can wait for R scripts within power BI and then be able to do any kind of statistical analysis <span class="lia-unicode-emoji" title=":slightly_smiling_face:">ðŸ™‚</span> but good job.</P>Tue, 03 Nov 2015 08:37:57 GMThttps://community.powerbi.com/t5/Desktop/Simple-Linear-Regression-with-DAX/m-p/9363#M1775mstefancik2015-11-03T08:37:57ZRe: Simple Linear Regression with DAX
https://community.powerbi.com/t5/Desktop/Simple-Linear-Regression-with-DAX/m-p/11865#M2704
<P>I was following along with this and think I understand the basic calculation. Do you mind providing an example for how X and Y relate to a real world scenario. Am I correct to assume X may be time and Y a sales $ amount? </P>Wed, 09 Dec 2015 13:56:50 GMThttps://community.powerbi.com/t5/Desktop/Simple-Linear-Regression-with-DAX/m-p/11865#M2704Luke-Bongiorno2015-12-09T13:56:50ZRe: Simple Linear Regression with DAX
https://community.powerbi.com/t5/Desktop/Simple-Linear-Regression-with-DAX/m-p/13056#M3241
<P>Thx for the example!</P><P> </P><P>I was able to use your example to do a simple lineair regression forecast using a calender table. For "X" I used the date numbers (E.g.: 20150101), which is an extra column 'DateNumber' in my date table that displays the date as a number. Because I need future dates in my calender table to plot the forecast against, I had to add an extra FILTER to exclude future years from the sample being used in the calculation.</P><P> </P><PRE>Count:=COUNTAX(FILTER(ALL('Calender'); YEAR(Calender[DATE]) < YEAR(NOW()));'Calender'[DateNumber])</PRE>Tue, 22 Dec 2015 23:44:17 GMThttps://community.powerbi.com/t5/Desktop/Simple-Linear-Regression-with-DAX/m-p/13056#M3241Randy2015-12-22T23:44:17ZRe: Simple Linear Regression with DAX
https://community.powerbi.com/t5/Desktop/Simple-Linear-Regression-with-DAX/m-p/69784#M28928
<P>That's very useful I think. I want to add a couple of things:</P><P> </P><P>I plotted the sales v profit graph for my data, added the trendline and then, using the results of your DAX, put the intercept and slope values in a Card ... very easy to do I am sure you agree!</P><P> </P><P>To take this a step further, we should also look at the <STRONG>residuals</STRONG> so I created a new column for that, which in my case is</P><P> </P><P>Residuals = sales_profit[Sales]-sales_profit[Profit]</P><P> </P><P>I can then easily plot the estimates or fitted values on a new graph on the X axis and the residuals on the Y axis</P><P> </P><P>I haven't done this yet but the next step might be to add another column for <STRONG>Studentised Residuals</STRONG>: residual/standard deviation of residuals ... then use that for further analysis.</P><P> </P><P>I am aware that we can do all of this in R in Power BI but I still think you did us all a favour!</P>Tue, 20 Sep 2016 04:44:04 GMThttps://community.powerbi.com/t5/Desktop/Simple-Linear-Regression-with-DAX/m-p/69784#M28928duncanwil2016-09-20T04:44:04ZRe: Simple Linear Regression with DAX
https://community.powerbi.com/t5/Desktop/Simple-Linear-Regression-with-DAX/m-p/104046#M43565
<P>Thank You very much for the solution.</P><P> </P><P>As say a partner .... </P><P> </P><P><SPAN>"I was following along with this and think I understand the basic calculation. Do you mind providing an example for how X and Y relate to a real world scenario. Am I correct to assume X may be <STRONG>time (measure agregate months, etc)</STRONG> and Y a sales $ amount <STRONG>(measure agregate ... "Amount / personal", ..... )? "</STRONG></SPAN></P><P> </P><P><SPAN>Any Solution???</SPAN></P><P> </P><P><SPAN>Thank you in advance</SPAN></P>Mon, 19 Dec 2016 16:52:24 GMThttps://community.powerbi.com/t5/Desktop/Simple-Linear-Regression-with-DAX/m-p/104046#M43565JavierP2016-12-19T16:52:24Z