# Power BI Blog: LINEST and LINESTX Now Added to Power BI

13 April 2023

*Welcome back to
this week’s edition of the Power BI blog series. This week, we look at two new DAX functions
just added to the Power BI DAX library.*

Two new statistical **DAX **functions have just been added to the Power BI repertoire: **LINEST** and **LINESTX**. These two functions
perform linear regression, leveraging the Least Squares method, to calculate a
straight line that best fits the given data and return a table describing that
line. These functions are especially
useful in predicting unknown dependent values (**y**) given known independent
values (**x**).

Both functions return a single-row table describing the line and additional statistics. The resulting table includes columns such as slopes, intercepts, standard errors and the coefficient of determination. The equation of the fitted line can be constructed as follows

**y = Slope1 * x _{1} + Slope2 * x_{2} + … + Intercept**.

The difference
between **LINEST** and **LINESTX** is that **LINEST** expects columns
of known **x** and **y **values to be provided, whereas **LINESTX** expects a table and expressions to be evaluated for each row of the table to
obtain the **x** and **y** values.

For the following
examples, consider the following data, which includes **Sales Amount** and Gross
National Product, **GNP_Per_Capita**:

In the example
below, we will use **LINESTX** to predict total sales based upon GNP per
capita:

**LinestX_example =**

**VAR CountryGNP =
SUMMARIZE(**

**Sales,**

**'GNP_Country'[Country],**

**'GNP_Country'[GNP_Per_Capita],**

**"Total
Sales", SUM(Sales[Sales Amount])**

**)**

** **

**VAR SalesPrediction
= LINESTX(**

**'CountryGNP',**

**[Total Sales],**

**[GNP_Per_Capita]**

**)**

** **

**VAR
Example_GNP_Per_Capita = 50000**

** **

**RETURN
SELECTCOLUMNS(**

**SalesPrediction,**

**[Slope1]**

**) *
Example_GNP_Per_Capita +**

**SELECTCOLUMNS(**

**SalesPrediction,**

**[Intercept]**

**)**

This expression not
only leverages **LINESTX** but also leverages the result to perform a
prediction for a fictitious country with gross national product per capita of
$50,000. The result is a predicted total
sales of $17,426,123.29. Of course, this
is a fabricated scenario and it’s rare to have a fixed value such as the
$50,000 above as part of the expression.

We may do the same
using **LINEST** assuming the required tables are all in the model, *e.g.* as calculated tables. In this example, we’ve
added the following calculated tables:

**CountryDetails**, defined as:

**CountryDetails
= SUMMARIZECOLUMNS(**

**'GNP_Country'[Country],**

**'GNP_Country'[GNP_Per_Capita],**

**"Total
Sales", SUM(Sales[Sales Amount]))**

**SalesPredictionLINEST,**defined as:

**SalesPredictionLINEST
= LINEST('CountryDetails'[Total Sales], 'CountryDetails'[GNP_Per_Capita])**.

Now we may use following measure expression to obtain the same result as above:

**Linest_example =**

**VAR
Example_GNP_Per_Capita = 50000**

** **

**RETURN**

**MAX (
SalesPredictionLINEST[Slope1] ) * Example_GNP_Per_Capita**

**+ MAX ( SalesPredictionLINEST[Intercept]
)**

In the meantime, please remember we offer training in Power BI which you can find out more about here. If you wish to catch up on past articles, you can find all of our past Power BI blogs here.