TRENDLINE OpenBI
Trend lines or curves of y on x (linear, polynomial, log, exp, power) with their confidence and prediction bands and R², as R's lm.
Group: Statistics. OpenBI’s own: DAX has no function of this name.
Syntax
TRENDLINE ( <table>, <y>, <x> [, <models>] [, <level>] [, <points>] )| Parameter | Type | Description |
|---|---|---|
table | Table | The rows to fit the curves to. |
y | Expression evaluated for each row | The value to explain, for each row. |
x | Expression evaluated for each row | The value it is plotted against, for each row. |
models | Text optional | Which curves to fit, at most eight, separated by spaces or commas: "linear" (the default), "quadratic" and "cubic", "poly:N" for a polynomial of degree N from 1 to 6, "log", "exp" and "power". |
level | Number optional | The confidence level of the bands, between 0 and 1; 0.95 when left out. |
points | Whole number optional | At how many values of x, evenly from the smallest to the largest, each curve is given: 2 to 10,000; 100 when left out. |
Returns
Table. A row a point of each curve: Model, X, Fit, Lower and Upper (the band for the curve itself), Prediction lower and Prediction upper (the band for one new value), R squared and Observations.
Remarks
- Select and order the columns with
SELECTCOLUMNS, as the examples do: the curve between its bands reads well. The bands are those of R's predict with lm: the confidence band says where the curve itself may run, the prediction band, always wider, where a single new value may fall. - log fits y against ln x, exp ln y against x, and power ln y against ln x. exp and power give the fit and the bands back in y's units; their R squared is that of the fit on the logarithms.
- log and power need every x above 0; exp and power every y above 0.
- A row with a blank x or y is left out. A curve needs more rows than it has coefficients: a line three, a cubic five.
Compared with DAX
An OpenBI function: DAX has none like it, so a measure or query that uses it works in OpenBI only, not in Power BI or Analysis Services.
Examples
A line through eight weeks, at three points
EVALUATE
VAR Weeks =
DATATABLE (
"Week", INTEGER, "Liters", INTEGER,
{ { 1, 410 }, { 2, 435 }, { 3, 470 }, { 4, 462 }, { 5, 505 }, { 6, 530 }, { 7, 528 }, { 8, 571 } }
)
RETURN
SELECTCOLUMNS (
TRENDLINE ( Weeks, [Liters], [Week], "linear", 0.95, 3 ),
"Model", [Model], "X", [X],
"Prediction lower", [Prediction lower], "Lower", [Lower], "Fit", [Fit], "Upper", [Upper],
"Prediction upper", [Prediction upper], "R squared", [R squared]
)| Model | X | Prediction lower | Lower | Fit | Upper | Prediction upper | R squared |
|---|---|---|---|---|---|---|---|
| linear | 1 | 379.194483442796 | 394.780803004987 | 413.25 | 431.719196995013 | 447.305516557204 | 0.959841505851749 |
| linear | 4.5 | 458.527012560475 | 478.759004186825 | 488.875 | 498.990995813175 | 519.222987439525 | 0.959841505851749 |
| linear | 8 | 530.444483442796 | 546.030803004987 | 564.5 | 582.969196995013 | 598.555516557204 | 0.959841505851749 |
Three curves compared at the ends
EVALUATE
VAR Weeks =
DATATABLE (
"Week", INTEGER, "Liters", INTEGER,
{ { 1, 410 }, { 2, 435 }, { 3, 470 }, { 4, 462 }, { 5, 505 }, { 6, 530 }, { 7, 528 }, { 8, 571 } }
)
RETURN
SELECTCOLUMNS (
TRENDLINE ( Weeks, [Liters], [Week], "linear quadratic exp", 0.95, 2 ),
"Model", [Model], "X", [X],
"Prediction lower", [Prediction lower], "Lower", [Lower], "Fit", [Fit], "Upper", [Upper],
"Prediction upper", [Prediction upper], "R squared", [R squared]
)| Model | X | Prediction lower | Lower | Fit | Upper | Prediction upper | R squared |
|---|---|---|---|---|---|---|---|
| linear | 1 | 379.194483442796 | 394.780803004987 | 413.25 | 431.719196995013 | 447.305516557204 | 0.959841505851749 |
| linear | 8 | 530.444483442796 | 546.030803004987 | 564.5 | 582.969196995013 | 598.555516557204 | 0.959841505851749 |
| quadratic | 1 | 370.008403100528 | 385.33164816336 | 413.041666666667 | 440.751685169973 | 456.074930232805 | 0.959848790125788 |
| quadratic | 8 | 521.258403100528 | 536.58164816336 | 564.291666666667 | 592.001685169973 | 607.324930232805 | 0.959848790125788 |
| exp | 1 | 387.462118347241 | 400.332176648324 | 416.137246899766 | 432.566299584358 | 446.93455194018 | 0.958421116887303 |
| exp | 8 | 529.015778680446 | 546.587725953258 | 568.166949186309 | 590.598117776402 | 610.215602553275 | 0.958421116887303 |