Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 🙂
Solved! Go to Solution.
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
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 ???
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
This is super helpful, thank you!
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |