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 * x1 + Slope2 * x2 + … + 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]
)
Be the first to comment