topic Re: Linear Regression (LINEST, LINESTX) possible for serial number and value? in DAX Commands and Tips

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

cousinitt13 — Tue, 05 Sep 2023 14:05:44 GMT

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
...	...	...

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

DataNinja777 — Wed, 06 Sep 2023 03:22:16 GMT

Hi cousinitt13,

I guess that you have already found this article:

Implementing linear regression in Power BI - SQLBI

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

cousinitt13 — Wed, 06 Sep 2023 06:52:27 GMT

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. 😞

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

DataNinja777 — Wed, 06 Sep 2023 10:35:35 GMT

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.

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

cousinitt13 — Thu, 07 Sep 2023 08:27:39 GMT

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

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

DataNinja777 — Thu, 07 Sep 2023 13:39:59 GMT

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.

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

cousinitt13 — Fri, 08 Sep 2023 06:01:44 GMT

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?

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

DataNinja777 — Fri, 08 Sep 2023 06:30:33 GMT

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?