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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 VAR

2. return the same output that I would get if I inputted these as the known x and known y in LOGEST() in excel--i.e., the exponential rate of growth.

 

Is this possible? Can't seem to find any documentation on it.

 

Thanks in advance for your help 🙂

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Well... Where do you take your VAR's from? Please note that VAR's in DAX are not something that you can pass in from outside to a measure. VAR's are there only to make a reference to something so that it's calculated once. Measures work on columns of tables and they change their values when filters on other columns change.

Not sure what you really want to achieve...

Best
D
Anonymous
Not applicable

Thanks for your response! I'm aware of all this, sorry if I wasn't clear. My desired output is the same output as the LOGEST() function in excel, i.e. the growth rate of an exponential curve fitted to an inputted list of known x and y values. I would be defining the VAR x_1 etc values within this measure purely for the purpose of calculating this output.

 

EDIT - in answer to where I get my VAR values from, I have one measure [COST] for which I have data over 5 years. I would set VAR y_1 equal to [COST] filtered to the first year, y_2 equal to [COST] filtered to the second year, etc. I have successfully done this to calculate other measure, eg year-over year change, linear trend, etc. The x_1, x_2, etc values would always be equal to 1,2,3,4,5. What I ultimately want to see is, for any given cut of the data, how the cost has been trending year over year using an exponential fit.

 

As a simplified example, if I input the following known x and y values to the LOGEST() function in excel, I get a growth rate of 2.402. What I'm wondering is if there's anything I can do in PowerBI with the same information to get the same output, or if I should just export my data to excel and work from there.

 

Growth = 

   VAR x_1 = 1

   VAR x_2 = 2

   VAR x_3 = 3

   VAR x_4 = 4

   VAR x_5 = 5

 

   VAR y_1 = 5

   VAR y_2 = 30

   VAR y_3 = 130

   VAR y_4 = 120

   VAR y_5 = 200

 

RETURN ???

Anonymous
Not applicable

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
Not applicable

This is super helpful, thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors