topic Re: Find the Power Law (or log) relationship between two variables in DAX Commands and Tips

Find the Power Law (or log) relationship between two variables

ChPetru — Mon, 14 Mar 2022 09:08:25 GMT

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

Re: Find the Power Law (or log) relationship between two variables

Greg_Deckler — Mon, 14 Mar 2022 11:08:55 GMT

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.

LR05/PowerLaw (pomona.edu)

You also might consider Runge-Kutta for finding unknown values:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Runge-Kutta/m-p/411280#M149

Re: Find the Power Law (or log) relationship between two variables

PiEye — Mon, 14 Mar 2022 11:35:37 GMT

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 🙂

Re: Find the Power Law (or log) relationship between two variables

ChPetru — Mon, 14 Mar 2022 11:51:45 GMT

Thank you!!

Re: Find the Power Law (or log) relationship between two variables

PiEye — Mon, 14 Mar 2022 13:20:57 GMT

Welcome!