#LINEAR REGRESSION EQUATION HOW TO#

Here is the definition of the LinearRegression measure we used to draw the linear regression line in our chart:

#LINEAR REGRESSION EQUATION HOW TO#

In both cases, our goal here is to explain how to consume the result of LINESTX in DAX.īecause in a DAX formula you are likely to use several of the values returned by LINESTX (slope and intercept in our case), the most efficient technique is to store the result in a variable and access each value by using SELECTCOLUMNS. Otherwise, you may find more information at Wolfram MathWorld. If you are from a statistics background, you probably already know these additional parameters. LINEST and LINESTX return more statistical information about the result of the Least Squares algorithm. The single row returned by the query has more columns than just slope and intercept. Indeed, we can execute the following DAX query: LINESTX can return multiple values by returning a table that has one row and multiple columns. We are used to aggregation functions returning a single scalar value. It actually returns more columns, though we only need these first two columns for now. LINESTX returns two values: slope and intercept. This is the LINESTX syntax to compute the slope and intercept parameters of the linear regression for our chart:ĪLLSELECTED ( Sales ), - Table with datapoints to iterate In this article, we consider the simplest case where we have a single expression for the X-axis. The first argument of LINESTX is the table to iterate for the calculation: for each row, there is an expression evaluated for the Y-axis and one or more expressions evaluated for the X-axis. The LINESTX function is the best candidate, as it allows us to specify the number of data points used to compute the linear regression. We want to compute the linear regression by considering all the values of Sales displayed in the chart. In our simple scenario, the goal is to produce the slope and intercept parameters for the following formula: Through the linear regression, we want to obtain the following result.

We start our example by analyzing for all transactions of our Contoso database, the average price for various quantities purchased.

This article describes the more generic function LINESTX. Internally, LINEST invokes LINESTX and provides to it the table that contains the column references specified in the LINEST arguments. LINEST gets column references as arguments, whereas LINESTX explicitly iterates over the table provided in the first argument and executes the other arguments in a row context. Both functions return multiple values, represented in a table that has a single row and one column for each of the values returned. LINEST and LINESTX are two DAX functions that calculate a linear regression by using the Least Squares method.

Linear regression equation