New LINEST & LINESTX DAX functions

In this blog post I describe the new DAX function, LINESTX in Power BI and test it in a brief example.

With the February Power BI update, two new DAX statistics functions have been introduced: LINEST and LINESTX.

The functions use the Least Squares method to calculate a straight line that best fits the data, then return a table describing the line. The data result from expressions evaluated for each row in a table.

These functions are particularly useful for predicting unknown values (Y) from known values (X) values.

• LINEST ( Value Y, Value X)

• LINESTX ( table, column Y, column X )

Both functions return a single row table describing the line with additional statistics informations. The resulting table includes columns such as slopes, intercepts, standard errors and coefficient of determination.

• Slope1, Slope2, ..., SlopeN: the coefficients corresponding to each x-value

• Intercept: intercept value

The fitted line equation can be constructed: Y = Slope1 * X1 + Slope2 * X2 + ...+ Intercept.

Example:

To test the LINESTX function, I used the following data, which includes sales and costs by product category:

In the example below, I use LINESTX to predict total sales based on estimated cost.

The purpose is to introduce new categories, setting their estimated costs, and then use LINESTX to forecast their sales.

First thing, I added a disconnected table with the new categories to be evaluated:

In the DAX code below, I use LINESTX to predict total sales based on sales and costs for existing categories:

To exploit the returned result, I used the following equation:

• Y = Slope1 * X + Intercept.

• Expected Sales = Slope1 * Estimated Cost + Intercept

This expression exploits LINESTX from the input data of sales and costs of existing categories, and then uses its output to make a prediction for a given product category whose cost is estimated beforehand. The result is a prediction of total sales for each new category being evaluated.

Result

For a given new category, this model predicts the sale amount

Summary

In summary, the DAX LINESTX/ LINEST (Y, X) functions perform a linear regression, based on the least squares method, in order to create a straight line that best fits our provided data context based on Y and X values.

The functions return a single row table with columns containing statistical information data that describe the line.

The resulting output can be used in an equation: Y = Slope1 * X + Intercept to estimate results based on our existing data.