LINESTX

LINESTX is a DAX function that calculates the best-fit straight line using the Least Squares method based on expressions evaluated for each row in a table and returns a table describing the line.

Syntax

LINESTX(
   Table,
   ExpressionY,
   [ ExpressionX, ... ],
   Const
)

Argument	Properties	Description
Table		The table containing the rows for which the expressions will be evaluated.
ExpressionY		The expression to be evaluated for each row of the table, to obtain the known y-values.
ExpressionX	Repeatable	The expressions to be evaluated for each row of the table, to obtain the 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.

Return Values

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.

The following DAX query:

DEFINE VAR TotalSalesByRegion = SUMMARIZECOLUMNS(
    'Sales Territory'[Sales Territory Key],
    'Sales Territory'[Population],
    "Total Sales", SUM(Sales[Sales Amount])
)
EVALUATE LINESTX(
    'TotalSalesByRegion',
    [Total Sales],
    [Population]
)

Returns a single-row table with ten columns:

Slope1	Intercept	StandardErrorSlope1	StandardErrorIntercept	CoefficientOfDetermination
6.42271517588	-410592.76216	0.24959467764561	307826.343996223	0.973535860750193

StandardError	FStatistic	DegreesOfFreedom	RegressionSumOfSquares	ResidualSumOfSquares
630758.1747292	662.165707642	18	263446517001130	7161405749781.07

• 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 sales territory, this model predicts total sales by the following formula:

Total Sales = Slope1 * Population + Intercept

DEFINE VAR TotalSalesByCustomer = SUMMARIZECOLUMNS(
    'Customer'[Customer ID],
    'Customer'[Age],
    'Customer'[NumOfChildren],
    "Total Sales", SUM(Sales[Sales Amount])
)
EVALUATE LINESTX(
    'TotalSalesByCustomer',
    [Total Sales],
    [Age],
    [NumOfChildren]
)