Forum Discussion
Formula for trendline
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-to-charts
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
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
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?
Hi Markus,
I need the formula for predictions...
I posted a suggestion in the Ideas section.
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-to-charts
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
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