topic Trend line values in Desktop
https://community.powerbi.com/t5/Desktop/Trend-line-values/m-p/1641410#M658965
<P>Hi,</P><P> </P><P>Simple enough question - I have some data, two columns, cost of sale and date of sale. I've plotted average cost of sale against time in a line graph. I've added a trend line from the analytics pane to show the overall changes over time - while I can look at roughly what the value is by comparing against the y-axis, I can't seem to find a way to get what the actual value is at any point in time. Is there a way to get this displayed, say as a tooltip, or failing that, does anyone know how Power BI calculates this trend line so I can try to replicate it as a DAX measure?</P>Wed, 03 Feb 2021 10:25:21 GMTjthomson2021-02-03T10:25:21ZTrend line values
https://community.powerbi.com/t5/Desktop/Trend-line-values/m-p/1641410#M658965
<P>Hi,</P><P> </P><P>Simple enough question - I have some data, two columns, cost of sale and date of sale. I've plotted average cost of sale against time in a line graph. I've added a trend line from the analytics pane to show the overall changes over time - while I can look at roughly what the value is by comparing against the y-axis, I can't seem to find a way to get what the actual value is at any point in time. Is there a way to get this displayed, say as a tooltip, or failing that, does anyone know how Power BI calculates this trend line so I can try to replicate it as a DAX measure?</P>Wed, 03 Feb 2021 10:25:21 GMThttps://community.powerbi.com/t5/Desktop/Trend-line-values/m-p/1641410#M658965jthomson2021-02-03T10:25:21ZRe: Trend line values
https://community.powerbi.com/t5/Desktop/Trend-line-values/m-p/1646737#M660327
<P>Hi <LI-USER uid="35806"></LI-USER> ,</P>
<P>The trend line calulation formula by DAX is like this:</P>
<LI-CODE lang="markup">Linear regression =
VAR Known =
FILTER (
SELECTCOLUMNS (
ALLSELECTED ( 'Table'[Date] ),
"Known[X]", 'Table'[Date],
"Known[Y]", CALCULATE ( SUM ( 'Table'[Sales] ) )
),
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] * Known[X] )
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 * Sum_X
)
VAR Intercept = Average_Y - Slope * Average_X
RETURN
SUMX ( DISTINCT ( 'Table'[Date] ), Intercept + Slope * 'Table'[Date] )</LI-CODE>
<P>It will be the same as the trend line in the Analysis pane:</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="re.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/448532i3E3E3580C33A0468/image-size/medium?v=v2&px=400" role="button" title="re.png" alt="re.png" /></span></P>
<P>Attached a sample file in the below, hopes to help you.</P>
<P> </P>
<P>In addtion, this blog introduces the calculation logic in details that you can refer: <A href="https://xxlbi.com/blog/simple-linear-regression-in-dax/" target="_self">Simple linear regression in DAX</A> </P>
<P>Similiar issue: <A href="https://community.powerbi.com/t5/Desktop/DAX-to-create-a-Trend-line/td-p/398438" target="_self">DAX to create a Trend line?</A> </P>
<P> </P>
<P>Best Regards,<BR />Community Support Team _ Yingjie Li<BR />If this post <STRONG>helps</STRONG>, then please consider <STRONG><I>Accept it as the solution</I></STRONG> to help the other members find it more quickly.</P>Fri, 05 Feb 2021 01:56:18 GMThttps://community.powerbi.com/t5/Desktop/Trend-line-values/m-p/1646737#M660327v-yingjl2021-02-05T01:56:18Z