Linear Regression (LINEST, LINESTX) possible for serial number and value?

[cousinitt13]

Hallo, 

I'd like to to a trend or linear regression by grouped by Variable, see example below.
my problem is, that my linear regression value looks exactly the same as the values itself, how this can happen? 
hope for help cause I'm struggling since days. 

Thanks

SerialNumber	Variable	Value
101	thickness	5
102	wight	6
102	thickness	4
...	...	...

[cousinitt13]

Hi Sakiko, 
Yes, I have. This is not working for me. I get the same linear regression values like the values itself. no average line.  😞

[DataNinja777]

Hi @cousinitt13 

I copied and pasted that formula in the article and subsituted with your field and amount, and I am thinking the reason of the error could be because the x-axis is a text type which begins with 0.  I think in order to express the correlation between x-and y-axis, both have to be numerical values.    

[cousinitt13]

Hi Sakiko, 
Thanks for the feedback, the trendline from the chart is now available, but my measure is still wrong.
I've created a calculated table now, where I have all filtered information from the basic table, 

LinTable =   
    SUMMARIZE(
        KeepFilters(Quality_DEAT),
        Quality_DEAT[ProductCode],
        Quality_DEAT[VariableName],
        Quality_DEAT[EventNumber_ID],        
        Quality_DEAT[ResultonLocal],
        "Result",AVERAGE(Quality_DEAT[Result]))

and the calculated measure

LinearRegression Value =
VAR linreg =
    LINESTX (
        KEEPFILTERS(LinTable),        
        AVERAGE ( LinTable[Result]),
        LinTable[EventNumber_ID]
    )
VAR slope = SELECTCOLUMNS ( linreg, [Slope1] )
VAR intercept = SELECTCOLUMNS ( linreg, [Intercept] )
VAR x = SELECTEDVALUE ( LinTable[Result] )
VAR y = x * slope + intercept
RETURN
    y

and still getting value = linearregression value

BR
Roman

[DataNinja777]

Hi @cousinitt13 

My visualization also comes out like yours in a zigzag manner, and not liner. BTW, are you trying to visualize the correlation between the result amount and event ID number?  As event ID is only an identifier, I guess there is no meaningful correlation between the two variables.  I am guessing that you need to do this regression analysis for variables which have correlation between each other for it to work.  Please let me know if I am misunderstanding the objective of this task.  

[cousinitt13]

Maybe my fault to explain correctly. I want to have a trendline, from beginning of the shown data till the end of the shown data. The data itself are like temperature data, but instead of the date, you have a serial number. so my thoughts. For my understanding, you can do a trend also with temperature and time data, so it should work also for my kind of data or?

[DataNinja777]

Hi @cousinitt13,

Yes, I think so.  It sounds like a time series analysis using time as independent variable.  Instead of ID, what about using time dimension as your independent variable?  

[DataNinja777]

Hi cousinitt13,

I guess that you have already found this article:  

Implementing linear regression in Power BI - SQLBI