Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
cousinitt13
Helper I
Helper I

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

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

SerialNumberVariableValue
101thickness5
102wight6
102thickness4
.........

 

cousinitt13_0-1693922578797.png

 

7 REPLIES 7
cousinitt13
Helper I
Helper I

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

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.    

Sakiko_0-1693996124268.png

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
cousinitt13_0-1694075058984.png

BR
Roman

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.  

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?

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
Super User
Super User

Hi cousinitt13,

I guess that you have already found this article:  

Implementing linear regression in Power BI - SQLBI

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.