topic Re: Simple Linear Regression in Quick Measures Gallery
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/412906#M151
<P>Thanks for the measure Daniil, it is extremely helpful.</P>
<P> </P>
<P>I am attempting to apply it to a forecasting problem that is relatively simple in excel, but is causing some trouble for me with Dax/PowerBI. I have 19 different categories that I need to run the regression on, and then I need to sum those categories for use on further regressions. Could your measure be modified to accomplish this or do I need to create 19 different measures? </P>
<P> </P>
<P>Thanks for any help you could provide. </P>Tue, 08 May 2018 15:00:19 GMTjwjwjwjwjw2018-05-08T15:00:19ZSimple Linear Regression
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/247439#M55
<P>This measure allows you to predict dependent values Y from independent values X.</P>
<P> </P>
<H4 id="toc-hId--1323657085">NAME:</H4>
<P>Simple linear regression</P>
<P> </P>
<H4 id="toc-hId-419153250">DESCRIPTION:</H4>
<P>Estimate Y values based on X values.</P>
<P> </P>
<H4 id="toc-hId--2133003711">PARAMETERS:</H4>
<P>Name: Category</P>
<P>Tooltip: The category for which you have known X and Y values</P>
<P>Type: Categorical field</P>
<H4 id="toc-hId--2133003711"> </H4>
<P>Name: Measure X</P>
<P>Tooltip: Known X (independent) values</P>
<P>Type: Numerical field / measure</P>
<H4 id="toc-hId--2133003711"> </H4>
<P>Name: Measure Y</P>
<P>Tooltip: Known Y (dependent) values</P>
<P>Type: Numerical field / measure</P>
<H4 id="toc-hId--390193376"> </H4>
<H4 id="toc-hId-1352616959">DAX:</H4>
<P> </P>
<PRE>Estimated {Measure Y} =
VAR Known =
FILTER (
SELECTCOLUMNS (
ALLSELECTED ( <SPAN class="pastel-red">{Category}</SPAN> ),
"Known[X]", CALCULATE ( {Measure X} ),
"Known[Y]", CALCULATE ( {Measure Y} )
),
AND (
NOT ( ISBLANK ( Known[X] ) ),
NOT ( ISBLANK ( Known[Y] ) )
)
)
VAR Count_Items =
COUNTROWS ( Known )
VAR Sum_X =
SUMX ( Known, Known[X] )
VAR Sum_X2 =
SUMX ( Known, Known[X] ^ 2 )
VAR Sum_Y =
SUMX ( Known, Known[Y] )
VAR Sum_XY =
SUMX ( Known, Known[X] * Known[Y] )
VAR Average_X =
AVERAGEX ( Known, Known[X] )
VAR Average_Y =
AVERAGEX ( Known, Known[Y] )
VAR Slope =
DIVIDE (
Count_Items * Sum_XY - Sum_X * Sum_Y,
Count_Items * Sum_X2 - Sum_X ^ 2
)
VAR Intercept =
Average_Y - Slope * Average_X
RETURN
Intercept + Slope * {Measure X}</PRE>
<P> For more details on and other uses of this quick measure, see my blog post on the subject:</P>
<P><A title="XXL BI" href="https://xxlbi.com/blog/simple-linear-regression-in-dax/" target="_self">https://xxlbi.com/blog/simple-linear-regression-in-dax/</A></P>
<P> </P>
<P> </P>
<P><SPAN class="reportid hidden">eyJrIjoiZWNiNTQ2MGEtMjhlNS00YTNhLWE2NTktZDg3MzcxMjQ0NDc4IiwidCI6ImQzMmNkYzNmLTY1NTUtNGNhYy1iYjFhLTg2OWZiMTE0MzRlNSJ9</SPAN></P>Fri, 13 Apr 2018 06:19:22 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/247439#M55Daniil2018-04-13T06:19:22ZRe: Simple Linear Regression
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/311592#M70
<P>Hi</P>
<P>I am using your Simple Linear Regression measure with great success. Thank You.</P>
<P>I am doing temperature studies and I want to find the first and last values of your measure in a given time interval which is filtered by a slicer. By doing this I can calculate the difference in the trend line over the interval of filtered years.</P>
<P> </P>
<P>I can find the first and last <STRONG>year</STRONG> of the filtered interval by using FIRSTNONBLANK and LASTNONBLANK but I can't do this with your code because FIRSTNONBLANK and LASTNONBLANK only accept a column as an argument. I cannot seem to convert your measure into a calculated column to do this. Is there a version of your measure that produces a column? My data has two columns <STRONG>year</STRONG> and <STRONG>temperature. </STRONG>X and Y.</P>
<P> </P>
<P>Any help would be much appreciated. </P>
<P> </P>
<P>Tony Maclaren</P>Mon, 27 Nov 2017 13:16:15 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/311592#M70tonymaclaren2017-11-27T13:16:15ZRe: Simple Linear Regression
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/312407#M71
<P>Thanks for your feedback, Tony!</P>
<P> </P>
<P>If I understand you correctly, there are a few ways to achieve your goal.</P>
<P> </P>
<P>Here is an example of a measure:</P>
<PRE>Starting Temperature =
VAR Known =
FILTER (
SELECTCOLUMNS (
ALLSELECTED ( 'Table'[Year] ),
"Known[X]", 'Table'[Year] ),
"Known[Y]", [Temperature]
),
AND (
NOT ( ISBLANK ( Known[X] ) ),
NOT ( ISBLANK ( Known[Y] ) )
)
)
VAR First =
TOPN ( 1, Known, Known[X], ASC )
RETURN
MINX ( First, Known[Y] )</PRE>
<P> </P>Tue, 28 Nov 2017 08:59:14 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/312407#M71Daniil2017-11-28T08:59:14ZRe: Simple Linear Regression
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/312991#M72
<P>Dear Daniil</P>
<P>Thank you for your very prompt response. I will be in Sydney, where I grew up, in early December!</P>
<P>Here is the tweaked code below (minus some brackets) that does the trick.Thank you so much.</P>
<P>This is an<STRONG> incredibly useful</STRONG> companion measure to your Simple Linear Regression measure, and because it works with measures it will find the equivalent of FIRSTNONBLANK in <STRONG>a measure</STRONG>. However it does not work correctly in the filtered context of a YEAR slider to filter the interval of years which the Simple Linear Regression measure does .The value changes with the slider, but the results do not correctly match the estimated starting value of the <STRONG>Estimated</STRONG> measure except for the first value of the whole dataset.</P>
<P> </P>
<P>Starting Temperature = <BR />VAR Known =<BR /> FILTER (<BR /> SELECTCOLUMNS (<BR /> ALLSELECTED ( 'cetdata'[YEAR] ),<BR /> "Known[X]", 'cetdata'[YEAR],<BR /> "Known[Y]", [Estimated]<BR /> ),<BR /> AND (<BR /> NOT ( ISBLANK ( Known[X] ) ),<BR /> NOT ( ISBLANK ( Known[Y] ) )<BR /> )<BR /> )<BR />VAR First =<BR /> TOPN ( 1, Known, Known[X], ASC )<BR />RETURN<BR /> MINX ( First, Known[Y] )<BR /> NOT ( ISBLANK ( Known[Y] ) )<BR /> )<BR /> )<BR />VAR First =<BR /> TOPN ( 1, Known, Known[X], ASC )<BR />RETURN<BR /> MINX ( First, Known[Y] )</P>
<P> </P>
<P>Now we have to fix the filter context and figure out how to find [<STRONG>Ending temperature</STRONG>] so we can subtract <STRONG>[Starting Temperature]</STRONG> and get the trend change per filtered interval.</P>
<P> </P>
<P>Thanks again--great stuff. No one else had a clue what my problem was or how to solve it.</P>
<P> </P>
<P>Tony</P>
<P> </P>
<P> </P>Tue, 28 Nov 2017 19:35:08 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/312991#M72tonymaclaren2017-11-28T19:35:08ZRe: Simple Linear Regression
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/313299#M73
<P>Tony, you should visit the local Power BI User Group if you get a chance <span class="lia-unicode-emoji" title=":slightly_smiling_face:">ðŸ™‚</span> The next meeting date should be announced at <A title="Sydney Power BI User Group" href="https://www.meetup.com/en-AU/Sydney-Modern-Excel-Power-BI-User-Group/" target="_self">Meetup</A> soon.</P>
<P> </P>
<P>Try the following measures:</P>
<PRE>Starting Temperature =
VAR Estimate =
SELECTCOLUMNS (
KEEPFILTERS ( ALLSELECTED ( 'cetdata'[YEAR] ) ),
"Estimate[X]", 'cetdata'[YEAR],
"Estimate[Y]", [Estimated]
)
VAR First =
TOPN ( 1, Estimate, Estimate[X], ASC )
RETURN
MINX ( First, Estimate[Y] )</PRE>
<PRE>Ending Temperature =
VAR Estimate =
SELECTCOLUMNS (
KEEPFILTERS ( ALLSELECTED ( 'cetdata'[YEAR] ) ),
"Estimate[X]", 'cetdata'[YEAR],
"Estimate[Y]", [Estimated]
)
VAR Last =
TOPN ( 1, Estimate, Estimate[X], DESC )
RETURN
MAXX ( Last, Estimate[Y] )</PRE>
<PRE>Temperature Difference = [Ending Temperature] - [Starting Temperature]</PRE>Wed, 29 Nov 2017 03:24:32 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/313299#M73Daniil2017-11-29T03:24:32ZRe: Simple Linear Regression
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/313832#M74
<P>Dear Daniil</P>
<P>Thank you very much--all working perfectly now. KEEPFILTERS did the trick.I have really learned a lot! I have now ordered the SQLBI book.</P>
<P> </P>
<P>One strange thing however,The [Estimated] measure which is your Simple linear Regression measure produces a total if displayed in a table. I do not quite understand this.</P>
<P> </P>
<P>Best Regards</P>
<P>and thanks again</P>
<P> </P>
<P>Tony</P>Wed, 29 Nov 2017 13:43:29 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/313832#M74tonymaclaren2017-11-29T13:43:29ZRe: Simple Linear Regression
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/315666#M75
<P>Really excellent stuff !!</P>
<P>I had managed to do a linear regression line previously but this is so much cleaner </P>
<P>I did have to tweak yours slightly </P>
<P> </P>
<P>1) Instead of a plain old Allselected i needed to </P>
<P>do </P>
<P>CALCULATETABLE (<BR /> SUMMARIZE (<BR /> FeederHistory,<BR /> FeederHistory[EventDateHour],<BR /> FeederHistory[EventDate]<BR /> ),<BR /> ALLSELECTED ( FeederHistory )<BR /> ),</P>
<P>and for some reason SSRS moaned at me for using SELCTEDVALUE so I replaced it with the older hasonevalue values paradigm </P>
<P>ie</P>
<P>RETURN<BR /> Intercept<BR /> + Slope<BR /> * IF (<BR /> HASONEVALUE ( FeederHistory[EventDate] ),<BR /> VALUES ( FeederHistory[EventDate] ))</P>
<P> </P>
<P> </P>
<P> </P>Fri, 01 Dec 2017 12:45:12 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/315666#M75rdodworth2017-12-01T12:45:12ZRe: Simple Linear Regression
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/315668#M76
<P>Really excellent stuff !!</P>
<P>I had managed to do a linear regression line previously but this is so much cleaner </P>
<P>I did have to tweak yours slightly </P>
<P> </P>
<P>1) Instead of a plain old Allselected i needed to </P>
<P>do </P>
<P>CALCULATETABLE (<BR />SUMMARIZE (<BR />FeederHistory,<BR />FeederHistory[EventDateHour],<BR />FeederHistory[EventDate]<BR />),<BR />ALLSELECTED ( FeederHistory )<BR />),</P>
<P>and for some reason SSRS moaned at me for using SELCTEDVALUE so I replaced it with the older hasonevalue values paradigm </P>
<P>ie</P>
<P>RETURN<BR />Intercept<BR />+ Slope<BR />* IF (<BR />HASONEVALUE ( FeederHistory[EventDate] ),<BR />VALUES ( FeederHistory[EventDate] ))</P>
<P> </P>
<P> </P>Fri, 01 Dec 2017 12:48:43 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/315668#M76rdodworth2017-12-01T12:48:43ZRe: Simple Linear Regression
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/315670#M77
<P>I know this is a Power Bi portal but I had to make it work for both PBI and SSRS</P>Fri, 01 Dec 2017 12:51:56 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/315670#M77rdodworth2017-12-01T12:51:56ZRe: Simple Linear Regression
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/315795#M78
<P>Hi</P>
<P>Thanks for your comments. If, like me you ever need to get the first and last values for a given filtered interval to calculate the total trend change over time, or rate of trend change over time, the Starting and Ending measures are great. When you get something like this working it really puts a smile on your face! With these complicated measures I can highly recomment DAX formatter if you are not already using it.</P>
<P> </P>
<P><A href="http://www.daxformatter.com/" target="_blank">http://www.daxformatter.com/</A></P>
<P> </P>
<P>Tony</P>Fri, 01 Dec 2017 17:04:14 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/315795#M78tonymaclaren2017-12-01T17:04:14ZRe: Simple Linear Regression
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/315923#M79
<P><LI-USER uid="47472"></LI-USER>, it is likely that at the grand total level what you see is not a total, but the Intercept. This is because the following expression evaluates to Intercept:</P>
<PRE>Intercept + Slope * SELECTEDVALUE ( 'cetdata'[YEAR] )</PRE>
<P>At the grant total level, there is usually more than one 'cetdata'[YEAR], hence SELECTEDVALUE ( 'cetdata'[YEAR] ) returns BLANK, turning the multiplication into BLANK as well, leaving only Intercept.</P>
<P> </P>
<P>I updated my blog post to deal with this kind of situations.</P>
<P> </P>
<P><LI-USER uid="48531"></LI-USER>, glad you found this useful!</P>Sat, 02 Dec 2017 03:56:31 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/315923#M79Daniil2017-12-02T03:56:31ZRe: Simple Linear Regression
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/315951#M80
<P>Dar Daniil</P>
<P> </P>
<P>Thanks for the explanation.The same value also appears at the top of the [Estimated] measure, but only if the whole of the data for ('Ctedata'[YEAR])is displayed. is there a modification to eliminate this?</P>
<P> </P>
<P>Tony</P>Sat, 02 Dec 2017 10:26:30 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/315951#M80tonymaclaren2017-12-02T10:26:30ZRe: Simple Linear Regression
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/315962#M81
<P>Tony, it's a bit difficult to advise anything without seeing your report -- can you share a sample of your data?</P>Sat, 02 Dec 2017 11:20:05 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/315962#M81Daniil2017-12-02T11:20:05ZRe: Simple Linear Regression
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/315967#M82
<P>Hi Daniil</P>
<P> </P>
<P>Sure, no problem.What would you like me to supply and how.The full dataset is for 391 years.</P>
<P> </P>
<P>Tony</P>Sat, 02 Dec 2017 12:27:07 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/315967#M82tonymaclaren2017-12-02T12:27:07ZRe: Simple Linear Regression
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/315972#M83
<P>Wow, 391 years of data sounds exciting <span class="lia-unicode-emoji" title=":slightly_smiling_face:">ðŸ™‚</span> If you could reduce your data to the minimum amount with which you could reproduce your formula problems, that would be great.</P>Sat, 02 Dec 2017 13:34:48 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/315972#M83Daniil2017-12-02T13:34:48ZRe: Simple Linear Regression
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/315986#M84
<P>Thanks Daniil</P>
<P>I have attached a subset of the file that exhibits the exact same problem using the following measure:</P>
<P>Estimated tony Data = <BR />VAR Known =<BR /> FILTER (<BR /> SELECTCOLUMNS (<BR /> ALLSELECTED ( 'Tony_data'[YEAR] ),<BR /> "Known[X]", 'Tony_data'[YEAR],<BR /> "Known[Y]", [Averagetemp]<BR /> ),<BR /> AND (<BR /> NOT ( ISBLANK ( Known[X] ) ),<BR /> NOT ( ISBLANK ( Known[Y] ) )<BR /> )<BR /> )<BR />VAR Count_Items =<BR /> COUNTROWS ( Known )<BR />VAR Sum_X =<BR /> SUMX ( Known, Known[X] )<BR />VAR Sum_X2 =<BR /> SUMX ( Known, Known[X] ^ 2 )<BR />VAR Sum_Y =<BR /> SUMX ( Known, Known[Y] )<BR />VAR Sum_XY =<BR /> SUMX ( Known, Known[X] * Known[Y] )<BR />VAR Average_X =<BR /> AVERAGEX ( Known, Known[X] )<BR />VAR Average_Y =<BR /> AVERAGEX ( Known, Known[Y] )<BR />VAR Slope =<BR /> DIVIDE (<BR /> Count_Items * Sum_XY - Sum_X * Sum_Y,<BR /> Count_Items * Sum_X2 - Sum_X ^ 2<BR /> )<BR />VAR Intercept =<BR /> Average_Y - Slope * Average_X<BR />RETURN<BR /> Intercept + Slope * SELECTEDVALUE ( 'Tony_data'[YEAR])</P>
<P> </P>
<P> </P>
<P>Averagetemp = SUM(Tony_data[AverageTemperature])</P>
<P> </P>
<P>Tony</P>Sat, 02 Dec 2017 15:18:19 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/315986#M84tonymaclaren2017-12-02T15:18:19ZRe: Simple Linear Regression
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/316006#M85
<P>Tony, I can't reproduce the problem, unfortunately. Can you please attach a pbix file and screenshots with the problem highlighted?</P>
<P> </P>
<P>Also, have you followed my updated blog post? I don't use SELECTEDVALUE anymore: <A href="https://xxlbi.com/blog/simple-linear-regression-in-dax/" target="_blank">https://xxlbi.com/blog/simple-linear-regression-in-dax/</A></P>Sat, 02 Dec 2017 21:56:16 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/316006#M85Daniil2017-12-02T21:56:16ZRe: Simple Linear Regression
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/316130#M86
<P>Hi Daniil</P>
<P> </P>
<P>Thanks again for all your help. I have attached the whole file because it might interest you anyway. I sholuld stress it is a work in progress and my first use of Power BI. Please keep it to yourself.</P>
<P> </P>
<P>The page labeled <STRONG>Tony Data</STRONG> with the table in it shows the problem clearly--although it does not reproduce in the chart on that page. It is not really a problem, but it would be nice for us to understand the behaviour.Play around with the date slider and you will notice that if you set the start date to 1960<STRONG> only</STRONG> you see -32.72 at the <STRONG>top</STRONG> and <STRONG>bottom</STRONG> of the [Estimated Tony Data] column. Other start dates do not exhibit this problem.</P>
<P> </P>
<P>I will take a look at the blog.</P>
<P> </P>
<P>Tony</P>Sun, 03 Dec 2017 16:57:42 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/316130#M86tonymaclaren2017-12-03T16:57:42ZRe: Simple Linear Regression
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/404290#M140
<P>Aye Tony, hope my suggestion could work for you with such problem:</P>
<P> </P>
<P>You can try replacing this part:</P>
<P> </P>
<P><FONT color="#999999"> RETURN</FONT><BR /><FONT color="#999999"> Intercept + Slope * SELECTEDVALUE ( 'Tony_data'[YEAR])</FONT></P>
<P> </P>
<P>with this one:</P>
<P> </P>
<P><FONT color="#999999"> RETURN</FONT><BR /><FONT color="#999999"> SUMX( Tony_data , </FONT><BR /><FONT color="#999999"> Intercept + Slope * SELECTEDVALUE ( 'Tony_data'[YEAR])</FONT><BR /><FONT color="#999999"> )</FONT></P>
<P> </P>
<P><FONT color="#000000">Btw, this one is the very first reply of mine in this place, so HELLO WORLD then :'></FONT></P>Wed, 25 Apr 2018 14:41:23 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/404290#M140leanh2018-04-25T14:41:23ZRe: Simple Linear Regression
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/412906#M151
<P>Thanks for the measure Daniil, it is extremely helpful.</P>
<P> </P>
<P>I am attempting to apply it to a forecasting problem that is relatively simple in excel, but is causing some trouble for me with Dax/PowerBI. I have 19 different categories that I need to run the regression on, and then I need to sum those categories for use on further regressions. Could your measure be modified to accomplish this or do I need to create 19 different measures? </P>
<P> </P>
<P>Thanks for any help you could provide. </P>Tue, 08 May 2018 15:00:19 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/412906#M151jwjwjwjwjw2018-05-08T15:00:19ZRe: Simple Linear Regression
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/734624#M356
<P>Daniil, (or anyone else knowledgeable)</P>
<P>Many thanks for this but I am struggling a little with adapting it to my needs, as I am still quite new to DAX.</P>
<P>I am trying to create a sales prediction model and want to use the linear trend from just the last 12 months, I believe your current code will give me the linear regression from all of the data.</P>
<P>How do a go about just using the last 12 months of data to feed the regression.</P>
<P> </P>
<P>Many thanks for anyone that might be able to help,</P>
<P>Mark.</P>Mon, 08 Jul 2019 22:34:45 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/734624#M356mski_dev2019-07-08T22:34:45ZRe: Simple Linear Regression
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/782861#M387
<P>I reckon DAX might be cumbersome for this. You can consider installing python in Power BI to generate a linear model for your analysis. <LI-USER uid="26235"></LI-USER> </P>Tue, 03 Sep 2019 23:45:29 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/782861#M387Servus2019-09-03T23:45:29ZRe: Simple Linear Regression
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/930097#M410
<P>Hi <LI-USER uid="15597"></LI-USER>,</P>
<P> </P>
<P>I'm trying to find the slope of a graph comparing 2 pollution datasets. I altered your measure to do this. The measure works well until it comes to summing the X^2 and XY values. The multiplication doesn't work and this affects the slope calculation.</P>
<P> </P>
<P>What could be causing this problem?</P>
<P> </P>
<P>PS: I'm very inexperienced with Power BI, so forgive me if this is a stupid question related to SUM and SUMX.</P>Wed, 12 Feb 2020 12:15:44 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/930097#M410Upendoon2020-02-12T12:15:44ZRe: Simple Linear Regression
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/1770820#M689
<P>I've dabbled in multiple linear regression. Here's what it looks like with three x variables.</P>
<P> </P>
<LI-CODE lang="markup">Regression Coefficients =
VAR ShortNames =
SELECTCOLUMNS (
Returns,
"A", [Equity], /*Known X1 values*/
"D", [Duration], /*Known X2 values*/
"C", [Credit], /*Known X3 values*/
"Y", [Manager] /*Known Y values*/
)
VAR n = COUNTROWS ( ShortNames )
VAR A = SUMX ( ShortNames, [A] )
VAR D = SUMX ( ShortNames, [D] )
VAR C = SUMX ( ShortNames, [C] )
VAR Y = SUMX ( ShortNames, [Y] )
VAR AA = SUMX ( ShortNames, [A] * [A] ) - A * A / n
VAR DD = SUMX ( ShortNames, [D] * [D] ) - D * D / n
VAR CC = SUMX ( ShortNames, [C] * [C] ) - C * C / n
VAR AD = SUMX ( ShortNames, [A] * [D] ) - A * D / n
VAR AC = SUMX ( ShortNames, [A] * [C] ) - A * C / n
VAR DC = SUMX ( ShortNames, [D] * [C] ) - D * C / n
VAR AY = SUMX ( ShortNames, [A] * [Y] ) - A * Y / n
VAR DY = SUMX ( ShortNames, [D] * [Y] ) - D * Y / n
VAR CY = SUMX ( ShortNames, [C] * [Y] ) - C * Y / n
VAR BetaA =
DIVIDE (
AY*DC*DC - AD*CY*DC - AY*CC*DD + AC*CY*DD + AD*CC*DY - AC*DC*DY,
AD*CC*AD - AC*DC*AD - AD*AC*DC + AA*DC*DC + AC*AC*DD - AA*CC*DD
)
VAR BetaD =
DIVIDE (
AY*CC*AD - AC*CY*AD - AY*AC*DC + AA*CY*DC + AC*AC*DY - AA*CC*DY,
AD*CC*AD - AC*DC*AD - AD*AC*DC + AA*DC*DC + AC*AC*DD - AA*CC*DD
)
VAR BetaC =
DIVIDE (
- AY*DC*AD + AD*CY*AD + AY*AC*DD - AA*CY*DD - AD*AC*DY + AA*DC*DY,
AD*CC*AD - AC*DC*AD - AD*AC*DC + AA*DC*DC + AC*AC*DD - AA*CC*DD
)
VAR Intercept =
AVERAGEX ( ShortNames, [Y] )
- AVERAGEX ( ShortNames, [A] ) * BetaA
- AVERAGEX ( ShortNames, [D] ) * BetaD
- AVERAGEX ( ShortNames, [C] ) * BetaC
RETURN
{ BetaA, BetaD, BetaC, Intercept } /*Pick One*/</LI-CODE>
<P> </P>
<P>(From my <A href="https://stackoverflow.com/a/65597847/765226" target="_self">StackOverflow post</A>.)</P>
<P> </P>
<P> </P>Tue, 06 Apr 2021 21:46:37 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/m-p/1770820#M689AlexisOlson2021-04-06T21:46:37Z