LINESTX
A least-squares line through expressions over a table's rows, in one row: slopes, intercept, standard errors, R², F and sums of squares.
Group: Statistics. Compatible with DAX.
Syntax
LINESTX ( <table>, <expressionY>, <expressionX> [, <expressionX>] … [, <const>] )| Parameter | Type | Description |
|---|---|---|
table | Table | The rows to fit the line to. |
expressionY | Expression evaluated for each row | The value to explain, for each row. |
expressionX | Expression repeatable, evaluated for each row | A value that explains it, for each row; one or more. |
const | Boolean optional | TRUE or left out to fit an intercept; FALSE for a line through the origin. Written as TRUE or FALSE, last. |
Returns
Table. One row: Slope1 … SlopeN, Intercept, StandardErrorSlope1 … StandardErrorSlopeN, StandardErrorIntercept, CoefficientOfDetermination, StandardError, FStatistic, DegreesOfFreedom, RegressionSumOfSquares and ResidualSumOfSquares.
Remarks
- The expressions are worked out row by row, so they may use
RELATED, or measures: overVALUES ( Sales[Month] ),[Liters sold]is each month's own. - A blank y or x counts as 0: a row drops out only when all its values, y and every x, are blank (example 3). Leave a row out with
FILTERwhen a blank means missing. - The numbers are least squares as R's lm gives them: R squared, the F statistic and the standard errors included.
- An x that follows from the others (collinear), such as one twice another or a constant, gets a slope of 0 and a standard error of 0, and the others share the fit.
- With const FALSE there is no Intercept column, nor its standard error.
- No more rows than coefficients is an error: one x with an intercept needs three rows at least.
Examples
A trend through eight weeks
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
LINESTX ( Weeks, [Liters], [Week] )| Slope1 | Intercept | StandardErrorSlope1 | StandardErrorIntercept | CoefficientOfDetermination | StandardError | FStatistic | DegreesOfFreedom | RegressionSumOfSquares | ResidualSumOfSquares |
|---|---|---|---|---|---|---|---|---|---|
| 21.6071428571429 | 391.642857142857 | 1.80430795899268 | 9.11130857108631 | 0.959841505851748 | 11.6932520223051 | 143.407992686431 | 6 | 19608.4821428571 | 820.392857142855 |
Two explaining values
Liters a week against the price and the number of sunny days.
EVALUATE
VAR Shops =
DATATABLE (
"Liters", INTEGER, "Price", DOUBLE, "Sunny days", INTEGER,
{
{ 520, 38, 12 }, { 480, 40, 10 }, { 610, 36, 18 }, { 450, 42, 9 },
{ 590, 37, 15 }, { 500, 41, 14 }, { 560, 38, 13 }
}
)
RETURN
SELECTCOLUMNS (
LINESTX ( Shops, [Liters], [Price], [Sunny days] ),
"Per krone of price", [Slope1],
"Per sunny day", [Slope2],
"Intercept", [Intercept],
"R squared", [CoefficientOfDetermination]
)| Per krone of price | Per sunny day | Intercept | R squared |
|---|---|---|---|
| -16.5038167938932 | 8.18538713195199 | 1064.88113413305 | 0.960942873919975 |
A blank x counts as 0
The third row's x is blank: the line is the same as with 0 written there. Leaving the row out with FILTER gives another line.
EVALUATE
VAR Points =
DATATABLE (
"x", DOUBLE, "y", DOUBLE,
{ { 1, 2.5 }, { 2, 3.8 }, { BLANK (), 3.2 }, { 4, 8.1 }, { 5, 10.4 } }
)
VAR Zero = SELECTCOLUMNS ( Points, "x", IF ( ISBLANK ( [x] ), 0, [x] ), "y", [y] )
RETURN
UNION (
SELECTCOLUMNS ( LINESTX ( Points, [y], [x] ), "Rows", "Blank x", "Slope", [Slope1], "Intercept", [Intercept] ),
SELECTCOLUMNS ( LINESTX ( Zero, [y], [x] ), "Rows", "0 written", "Slope", [Slope1], "Intercept", [Intercept] ),
SELECTCOLUMNS (
LINESTX ( FILTER ( Points, NOT ISBLANK ( [x] ) ), [y], [x] ),
"Rows", "Row left out", "Slope", [Slope1], "Intercept", [Intercept]
)
)
ORDER BY [Rows]| Rows | Slope | Intercept |
|---|---|---|
| 0 written | 1.58720930232558 | 1.79069767441861 |
| Blank x | 1.58720930232558 | 1.79069767441861 |
| Row left out | 2.01 | 0.170000000000002 |