March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Register Now- Power BI forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

- Power BI forums
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- Find the Power Law (or log) relationship between t...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

03-14-2022
02:08 AM

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.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-14-2022
04:35 AM

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 🙂

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-14-2022
04:35 AM

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 🙂

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-14-2022
04:51 AM

Thank you!!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-14-2022
06:20 AM

Welcome!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-14-2022
04:08 AM

@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

Follow on LinkedIn

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

Announcements

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Check out the November 2024 Power BI update to learn about new features.

Top Kudoed Authors

User | Count |
---|---|

62 | |

30 | |

25 | |

22 | |

18 |