Formula for trendline

[Anonymous]

I am using the October download of PBI.

I can add a trendline for scattered plot and a line chart.

However, I cannot find the option to add the formula (just like in Excel).

Any idea?

Thanks,

Tamir

[Anonymous · Accepted solution]

Someone lese asked for it in May - see the comments on https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6998768-ability-to-add-trend-line...

You can likely do it in R until/if that feature is implemented in native Power BI visuals - e.g. http://stackoverflow.com/questions/24882209/how-to-get-trendline-equations-in-r

[Anonymous]

Hi @Anonymous 

As of now, there is no native format option that will give you the regression summary output (Y intercept, slope, R^2, ect.)

You can write some DAX to derive the equation or you can download the 'Craydec Regression Chart' visual from BPI marketplace.  This visual will give you the equation (Y= MX + B), R^2, and other regression output data.

Obviously, you can also export your data set to Excel to do the complete regression analysis (P-value, T-test, F-score, std error, and much more).

I hope this helps!

-Eric

[Gregory_Felton]

This is simple linear regression. Also, I did not figure this out, but it has been so long that I don't remember where I got it. When I find the guy's name, I'll credit him here.

Just replace your dates (colored blue), and the measure of interest (colored red).

Total Sales Trend =

VAR Known =

FILTER (

SELECTCOLUMNS (

CALCULATETABLE ( VALUES ( Dates[Date] ), ALLSELECTED( Dates) ),

"Known[X]", Dates[Date],

"Known[Y]", [Total 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] ^ 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

VAR Result = SUMX ( DISTINCT ( Dates[Date] ),

Intercept + Slope * Dates[Date]

)

RETURN

    IF(SELECTEDVALUE( Dates[Date] ) >= TODAY(),

BLANK(),

        Result)

[TrevorC]

This is a great solution and the code can be found here: https://xxlbi.com/blog/simple-linear-regression-in-dax/

I would really appreciate it if someone could tell me how to display the slope.

[mc2863]

Thanks for sharing, do you know how we could amend this to be dynamic and account for filtered data either in a table or in an actual visual.  We are aggregating data at a product level and would like for the calculated trend to represent the selected product.

[MEhrenmueller]

As far as I know, there is no possibility to specify a formula for the trend line.

In the most recent version there is a possibility to add different kinds of lines. Would one of these fit your needs?

[Anonymous]

Hi Markus,

I need the formula for predictions...

I posted a suggestion in the Ideas section.

[Anonymous · Accepted solution]

Someone lese asked for it in May - see the comments on https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6998768-ability-to-add-trend-line...

You can likely do it in R until/if that feature is implemented in native Power BI visuals - e.g. http://stackoverflow.com/questions/24882209/how-to-get-trendline-equations-in-r

[ImkeF · Accepted solution]

Hi there,

this function replicates the Excel-Trend-function, just without the possiblity to define your own slope and intercept:

 (YList as list, NoOfIntervalls as number) =>
let
Source = Table.FromColumns({YList}),
xAxis = Table.AddIndexColumn(Source, "Index", 1, 1),
Rename1 = Table.RenameColumns(xAxis,{{"Column1", "y"}, {"Index", "x"}}),
AvgX = List.Average(Rename1[x]),
AvgY = List.Average(Rename1[y]),
x = Table.AddColumn(Rename1, "xX", each [x]-List.Average(Rename1[x])),
y = Table.AddColumn(x, "yY", each [y]-List.Average(x[y])),
xy = Table.AddColumn(y, "xy", each [xX]*[yY]),
xXx = Table.AddColumn(xy, "xXx", each [xX]*[xX]),
a = List.Sum(xXx[xy])/List.Sum(xXx[xXx]),
b = AvgY-(a*AvgX),
ListIntervalls = {List.Max(Rename1[x])+1..List.Max(Rename1[x])+NoOfIntervalls},
TableIntervalls = Table.FromList(ListIntervalls, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Rename = Table.RenameColumns(TableIntervalls,{{"Column1", "x"}}),
Values = Table.AddColumn(Rename, "y", each [x]*a+b),
TREND = Table.Combine({Rename1,Values})
in
TREND

[joshcomputer1]

Hi @ImkeF

when I go to power query and enter  a blank table then put in this code it comes up to an entry screen where I can put in one column reference. Is there a way to enter the x and y?  I want the trend for x against y similar to a trendline in a scatterplot?

[ImkeF]

Sorry, but I don't understand your question.

If your values that form the basis of the trend don't exist in a table already, you can enter them under "Enter data" and reference that table instead.

All this function does is to create a table with x and y -values for the desired count of future periods and it has nothing to do with the visualisation itself. 

[joshcomputer1]

@ImkeF

Sorry if I was unclear. I have this on my screen. When I go to choose column, it only allows me to pick one column (x).  How do I give it the y-axis? I was thinking that I select one column for x and one for y.  I want the x to be UR and the y to be ASA trend. 

[ImkeF]

Thanks, now I understand. My solution creates a Y-axis and an index starting as 1. So when you add an index starting at 1 to your original table, you can merge those tables back together on it after that.

[HediSlim]

Hi ImkeF,

I am a newbie with Power BI and I don't understand where do we have to implement your formula  :

There : 

 

Or there :

And in wich language is your formule : M or DAX ?

Thanks for your understanding

Best regards

[ImkeF]

copy the code and paste it into the advanced editor of the query editor:

You might also find this video helpful:

http://community.powerbi.com/t5/Webinars-and-Video-Gallery/Power-BI-Forum-Help-How-to-integrate-M-co...

[perpor]

Are there other introductory resources for M language, functions, etc.? It can be useful but simple examples are needed ...

[ImkeF]

copy the code and paste it into the advanced editor of the query editor:

You might also find this video helpful:

http://community.powerbi.com/t5/Webinars-and-Video-Gallery/Power-BI-Forum-Help-How-to-integrate-M-co...

[HediSlim]

Hi ImkeF,

I am a newbie with Power BI and I don't understand where do we have to implement your formula  :

There : 

 

Or there :

And in wich language is your formule : M or DAX ?

Thanks for your understanding

Best regards

[Anonymous]

Thank you.

I guess it answers my question....

I still have to work out how to implement the solution 🙂

Tamir

[perpor]

I am using the trend-line, useful. But I can't find how to see the values of the line (the trend-line), how can I do ??