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
nufffins's avatar
nufffins
Frequent Visitor
3 years ago

Thank you for the answer. Here is sample RAW data.

TimeAB
00.1600390.151217
0.10.1400770.151217
0.20.1600390.151217
0.30.1400770.135
0.40.1400770.135
0.50.1400770.135
0.60.1600390.151217
0.70.1400770.151217
0.80.1600390.135
0.90.1600390.135
10.1400770.135
1.10.1600390.135
1.20.1600390.151217
1.30.1400770.135
1.40.1600390.216083
1.50.1201160.151217
1.60.1400770.167433
1.70.1201160.151217
1.80.1400770.135
1.90.1600390.135
20.1400770.135
2.10.1600390.151217
2.20.1600390.135
2.30.1201160.151217
2.40.1201160.151217
2.50.1600390.151217
2.60.1400770.135
2.70.1400770.118783
2.80.1600390.135
2.90.1600390.151217
30.1400770.135
3.10.1201160.151217
3.20.1600390.151217
3.30.1600390.102567
3.40.1600390.102567
3.50.1400770.151217
3.60.1400770.135
3.70.1201160.151217
3.80.1201160.151217
3.90.1400770.135
40.1600390.135
4.10.1600390.118783
4.20.1201160.135
4.30.1400770.135
4.40.1600390.135
4.50.1201160.151217
4.60.1201160.151217
4.70.1400770.135
4.80.1600390.135
4.90.1400770.151217
50.1400770.135


I have a lot more columns, each with a different name and more will be added in the future.
I use get data from folder to combine files and unpivoting to get the table as in the image in the original post. I want to automatize data analysis process in PowerBI and I am stuck with calculating the rate column. Previously I have done it in excel with the formula {=LINEST(LN(B2:B6);A2:A6;1;1)} to calculate rate as slope for 0.5 hour. B is column with A values and A is column with time values.

My end goal is to visualise in PowerBI the original data together with the rate for each type (A, B etc) as in the image for type A:

 

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