Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Possible to duplicate LOGEST() function in powerBI

Hi, all - first time posting, relatively new to using PowerBI for anything other than data pulls.   I'd like to define a measure as follows: 1. define x_1, x_2, ... x_5 and y_1, y_2, ... y_5 as VA...
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi there.

     

    Mate, it looks like you'd like to fit to your data points a curve of this shape:

    y(x) = b*m^x

    where b, m are unknown constants.

     

    This problem becomes a standard linear regression problem when you transform both sides with a logarithm:

    log(y(x)) = log(b*m^x)
log(y(x)) = log(b) + log(m) * x

     

    Now you can treat log(y(x)) as your new y-value and put log(b) = C, log(m) = B. Hence, you want to estimate B, C. Once you have these, you can do the reverse to get b and m.

     

    Your new linear regression equation is  y' = Bx + C. Now just calculate the estimates of B and C by the standard formulas for linear regression in one variable.

     

    Best

    D

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi there.

 

Mate, it looks like you'd like to fit to your data points a curve of this shape:

y(x) = b*m^x

where b, m are unknown constants.

 

This problem becomes a standard linear regression problem when you transform both sides with a logarithm:

log(y(x)) = log(b*m^x)
log(y(x)) = log(b) + log(m) * x

 

Now you can treat log(y(x)) as your new y-value and put log(b) = C, log(m) = B. Hence, you want to estimate B, C. Once you have these, you can do the reverse to get b and m.

 

Your new linear regression equation is  y' = Bx + C. Now just calculate the estimates of B and C by the standard formulas for linear regression in one variable.

 

Best

D

Anonymous's avatar
Anonymous
Not applicable
6 years ago

This is super helpful, thank you!