Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello,
I am trying to find a constant (equation) that describes the non-linear proportionality between two variables (below).
I need this constant because I am multiplying the second (Y) variable with a number, and want to see the corresponding X values.
Example: Y is 84,91% and X is 0.03. What would X be if Y would be 86%?
I can calculate this in excel by making a power law graphical trendline and use the trendline the equation as multiplier with Y values to get aproximate X.
Second question, how can I make a power law or log trendline in Powerbi? I can only see the linear trendline..
X Y
Solved! Go to Solution.
Hi, there isn't a default equation in DAX or setting in the line chart that will help you do that. There is, however a lot of guidance online that can tell you how to calculate a straight line gradient / regression. See this great example here: https://xxlbi.com/blog/simple-linear-regression-in-dax/.
This can be applied to the logs.
The logs of your X / Y values have a linear relationship which looks like this:
Using the guidance in the blog, I can apply the linear logic to those logs and calculate a straight line relationship:
Applying I can then reverse this estimated log of Y back into a predicted value for Y and fit a curve on the XY chart:
My expressions are as follows:
EstY = 10^('Table'[EstLogY])
EstLogY =
VAR Known =
FILTER (
SELECTCOLUMNS (
ALLSELECTED ( 'Table' ),
"Known[X]", 'Table'[logX],
"Known[Y]", 'Table'[logY]
),
AND (
NOT ( ISBLANK ( Known[X] ) ),
NOT ( ISBLANK ( Known[Y] ) )
)
)
VAR Count_Items =
COUNTROWS ( Known )
VAR Sum_X =
SUMX ( Known, Known[X] )
VAR Sum_X2 =
SUMX ( Known, Known[X] ^ 2 )
VAR Sum_Y =
SUMX ( Known, Known[Y] )
VAR Sum_XY =
SUMX ( Known, Known[X] * Known[Y] )
VAR Average_X =
AVERAGEX ( Known, Known[X] )
VAR Average_Y =
AVERAGEX ( Known, Known[Y] )
VAR Slope =
DIVIDE (
Count_Items * Sum_XY - Sum_X * Sum_Y,
Count_Items * Sum_X2 - Sum_X ^ 2
)
VAR Intercept =
Average_Y - Slope * Average_X
RETURN
SUMX (
DISTINCT ( 'Table'[Index]),
Intercept + Slope * 'Table'[lnX] )
Hope this helps!
Pi Eye 🙂
Hi, there isn't a default equation in DAX or setting in the line chart that will help you do that. There is, however a lot of guidance online that can tell you how to calculate a straight line gradient / regression. See this great example here: https://xxlbi.com/blog/simple-linear-regression-in-dax/.
This can be applied to the logs.
The logs of your X / Y values have a linear relationship which looks like this:
Using the guidance in the blog, I can apply the linear logic to those logs and calculate a straight line relationship:
Applying I can then reverse this estimated log of Y back into a predicted value for Y and fit a curve on the XY chart:
My expressions are as follows:
EstY = 10^('Table'[EstLogY])
EstLogY =
VAR Known =
FILTER (
SELECTCOLUMNS (
ALLSELECTED ( 'Table' ),
"Known[X]", 'Table'[logX],
"Known[Y]", 'Table'[logY]
),
AND (
NOT ( ISBLANK ( Known[X] ) ),
NOT ( ISBLANK ( Known[Y] ) )
)
)
VAR Count_Items =
COUNTROWS ( Known )
VAR Sum_X =
SUMX ( Known, Known[X] )
VAR Sum_X2 =
SUMX ( Known, Known[X] ^ 2 )
VAR Sum_Y =
SUMX ( Known, Known[Y] )
VAR Sum_XY =
SUMX ( Known, Known[X] * Known[Y] )
VAR Average_X =
AVERAGEX ( Known, Known[X] )
VAR Average_Y =
AVERAGEX ( Known, Known[Y] )
VAR Slope =
DIVIDE (
Count_Items * Sum_XY - Sum_X * Sum_Y,
Count_Items * Sum_X2 - Sum_X ^ 2
)
VAR Intercept =
Average_Y - Slope * Average_X
RETURN
SUMX (
DISTINCT ( 'Table'[Index]),
Intercept + Slope * 'Table'[lnX] )
Hope this helps!
Pi Eye 🙂
Thank you!!
Welcome!
@ChPetru Well, I believe the formula for Power Law is:
n = (logT2 - logT1) / (logM2 - logM1) is that what you are looking for? Here is a good paper on the math involved.
You also might consider Runge-Kutta for finding unknown values:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Runge-Kutta/m-p/411280#M149
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
21 | |
19 | |
13 | |
12 |
User | Count |
---|---|
42 | |
28 | |
23 | |
22 | |
22 |