Forum Discussion

nufffins's avatar
nufffins
Frequent Visitor
3 years ago

Slope from LINEST as new column

Hi! I need help with creating a new column in PowerBI. My data has columns for type, time and result and I need to calculate the rate for the result in new column. This is outtake fr...
Need Help
lbendlin's avatar
lbendlin
Super User
3 years ago

Thank you for the sample data. 

 

1) you can use TOPN(5,...)  for your example, or one of the new windowing functions.

2) to render the result are you only looking to plot the intercepts?

nufffins's avatar
nufffins
Frequent Visitor
3 years ago

Thanks for answer!

1) Could you please explain a bit more how to use this function? Where should I write it?
2) I'm not sure I understand the question. I'm using slope for ploting (to desribe exponential growth) not intercepts. 

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    This is what I have so far and it doesn't look good

    That would mean you would have to prepopulate a table with the windowed timestamps.

     

    Looking into LINESTX, and into CALCULATETABLE.

    • lbendlin's avatar
      lbendlin
      Super User
      3 years ago

      This works:

       

      LINEST A = 
var a = max('Table'[Time])
var b = TOPN(5,CALCULATETABLE('Table','Table'[Time]<=a),[Time],DESC)
var c = LINESTX(b,[Time],[A])
return sumx(c,[Slope1])

       

       

      but the result is most likely not what you wanted.

       

       

      • nufffins's avatar
        nufffins
        Frequent Visitor
        3 years ago

        Thanks for looking in to it.
        I think it should be:  var c = LINESTX(b, LN([A]), [Time])
        However, using the whole code gives me empty column.

        I made a sample PowerBI including
        1) rates calculated in excel for comparison

        2) "Result" query with the result I want.

        It is uploaded here: https://we.tl/t-Z4SHQi0d30