LINEST - Daxplained

LINEST is a DAX function that calculates the best-fit straight line using the Least Squares method and returns a table describing the line.

Syntax

LINEST(
   ColumnY,
   [ ColumnX, ... ],
   Const
)

Argument	Properties	Description
ColumnY		The column of known y-values.
ColumnX	Repeatable	The columns of known x-values.
Const	Optional	A constant TRUE/FALSE value specifying whether to force the constant b to equal 0. If true or omitted, b is calculated normally. If false, b is set to 0.

A single-row table describing the line, plus additional statistics. These are the available columns:

Slope1, Slope2, …, SlopeN: the coefficients corresponding to each x-value;
Intercept: intercept value;
StandardErrorSlope1, StandardErrorSlope2, …, StandardErrorSlopeN: the standard error values for the coefficients Slope1, Slope2, …, SlopeN;
StandardErrorIntercept: the standard error value for the constant Intercept;
CoefficientOfDetermination: the coefficient of determination (r²). Compares estimated and actual y-values, and ranges in value from 0 to 1: the higher the value, the higher the correlation in the sample;
StandardError: the standard error for the y estimate;
FStatistic: the F statistic, or the F-observed value. Use the F statistic to determine whether the observed relationship between the dependent and independent variables occurs by chance;
DegreesOfFreedom: the degrees of freedom. Use this value to help you find F-critical values in a statistical table, and determine a confidence level for the model;
RegressionSumOfSquares: the regression sum of squares;
ResidualSumOfSquares: the residual sum of squares.

columnY and the columnX’s must all belong to the same table.

The following DAX query:

EVALUATE LINEST(
'FactInternetSales'[SalesAmount],
'FactInternetSales'[TotalProductCost]
)

Returns a single-row table with ten columns:

Slope1	Intercept	StandardErrorSlope1	StandardErrorIntercept	CoefficientOfDetermination
1.67703250456677	6.34550460373026	0.000448675725548806	0.279131821917317	0.995695557281456

StandardError	FStatistic	DegreesOfFreedom	RegressionSumOfSquares	ResidualSumOfSquares
60.9171030357485	13970688.6139993	60396	51843736761.658	224123120.339218

Slope1 and Intercept: the coefficients of the calculated linear model;
StandardErrorSlope1 and StandardErrorIntercept: the standard error values for the coefficients above;
CoefficientOfDetermination, StandardError, FStatistic, DegreesOfFreedom, RegressionSumOfSquares and ResidualSumOfSquares: regression statistics about the model.

For a given internet sale, this model predicts the sale amount by the following formula:

For a given customer, this model predicts total sales by the following formula (the birth date is automatically converted to a number):

Related functions

Other functions related to LINEST are: