Edit

Share via

LINESTX

  • Article

Applies to: Calculated column Calculated table Measure Visual calculation

Uses the Least Squares method to calculate a straight line that best fits the given data, then returns a table describing the line. The data result from expressions evaluated for each row in a table. The equation for the line is of the form: y = Slope1*x1 + Slope2*x2 + ... + Intercept.

Syntax

LINESTX ( <table>, <expressionY>, <expressionX>[, …][, <const>] )

Parameters

Term Definition
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. Must have scalar type.
expressionX The expressions to be evaluated for each row of the table, to obtain the known x-values. Must have scalar type. At least one must be provided.
const (Optional) A constant TRUE/FALSE value specifying whether to force the constant Intercept to equal 0.
If TRUE or omitted, the Intercept value is calculated normally; If FALSE, the Intercept value is set to zero.

Return value

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.

Example 1

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

Example 2

The following DAX query:

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

Returns a single-row table with twelve columns:

Slope1 Slope2 Intercept StandardErrorSlope1
69.0435458093763 33.005949841721 -871.118539339539 0.872588875481658
StandardErrorSlope2 StandardErrorIntercept CoefficientOfDetermination StandardError
6.21158863903435 26.726292527427 0.984892920482022 68.5715034014342
FStatistic DegreesOfFreedom RegressionSumOfSquares ResidualSumOfSquares
3161.91535144391 97 29734974.9782379 456098.954637092

For a given customer, this model predicts total sales by the following formula:

Total Sales = Slope1 * Age + Slope2 * NumOfChildren + Intercept

Related content

LINEST Statistical functions