在 Power BI 中实现线性回归

本文翻译自Marco Russo & Alberto Ferrari的文章—《Implementing linear regression in Power BI》来源:SQLBI LINESTX DAX函数简化了线性回归的计算。这篇文章解释了有效管理其结果的正确方法。

LINEST和LINESTX是两个DAX函数,它们使用最小二乘法计算线性回归。这两个函数都返回多个值,用一个表格来表示,这个表格有一行,每个返回值都对应着其中的一列。

LINEST接受列引用作为参数,而LINESTX则明确地在第一个参数中提供的表格上进行迭代,并在行上下文中执行其他参数。在内部,LINEST调用LINESTX并向其提供包含在LINEST参数中指定的列引用的表格。本文介绍了更通用的LINESTX函数。

在我们的例子中,我们首先对Contoso数据库的所有交易进行分析,分析各种购买数量的平均价格。

通过线性回归,我们希望得到以下结果。

在我们这个简单的情景中,目标是为以下公式产生斜率和截距参数:

Y = X * slope + intercept

我们要通过考虑图表中显示的所有Sales[Quantity]值来计算线性回归。LINESTX函数是最佳选择,因为它允许我们指定用于计算线性回归的数据点数

LINESTX函数的第一个参数是要进行计算的表格:对于每一行,都有一个表达式用于计算Y轴,一个或多个表达式用于计算X轴。在本文中,我们考虑了最简单的情况,即X轴只有一个表达式的情况。以下是计算我们图表线性回归的斜率和截距参数的LINESTX语法:

LINESTX (

ALLSELECTED ( Sales[Quantity] ), -- Table with datapoints to iterate

[Avg Price], -- Expression for the Y-axis

Sales[Quantity] -- Expression for the X-axis

)

LINESTX函数返回两个值:斜率和截距。实际上,它返回更多列,虽然我们现在只需要这两列。我们习惯于聚合函数返回一个标量值。LINESTX可以通过返回一个一行多列的表来返回多个值。我们可以执行以下DAX查询:

DEFINE

MEASURE Sales[Avg Price] = [Sales Amount] / [Total Quantity]

EVALUATE

LINESTX (

ALL ( Sales[Quantity] ),

[Avg Price],

Sales[Quantity]

)


查询返回的单行有很多列,且不仅仅是斜率和截距。

LINEST和LINESTX返回最小二乘算法结果的更多统计信息。如果您有统计学背景,您可能已经了解这些附加参数。否则,您可以在 Wolfram MathWorld 上找到更多信息。在这两种情况下,我们的目标是解释如何在DAX中使用LINESTX的结果。

因为在DAX公式中,您可能会使用LINESTX返回的多个值(我们的情况是使用斜率和截距),最高效的方法是将结果存储在一个变量中,并通过使用SELECTCOLUMNS访问每个值。以下是我们用于绘制图表中线性回归线的LinearRegression度量的定义:

LinearRegression =

VAR line =

LINESTX (

ALL ( Sales[Quantity] ),

[Avg Price],

Sales[Quantity]

)

VAR slope = SELECTCOLUMNS ( line, [Slope1] )

VAR intercept = SELECTCOLUMNS ( line, [Intercept] )

VAR x = SELECTEDVALUE ( Sales[Quantity] )

VAR y = x * slope + intercept

RETURN y

intercept变量检索的是Intercept列的值。SELECTCOLUMNS的结果是一个只有一行(因为LINESTX只返回一行)和一列(因为我们只指定了一个列参数)的表格。由于DAX提供的自动转换功能,当我们在y的最终计算中使用斜率和截距变量时,这些只有一行和一列的表格会自动转换为相应的标量值。在其他语言中,我们会这样写代码:

VAR slope = line[Slope1]

VAR intercept = line[Intercept]

然而,DAX没有提供访问包含表格的变量的列引用语法。因此,我们需要由SELECTCOLUMNS提供的较长语法。

还有一个重要的细节需要解释,那就是为什么我们使用“Slope1”作为列名来检索斜率值,而不是只使用“Slope”。原因在于,LINEST和LINESTX是接受多个X轴值系列的函数,生成的公式对于每个X轴值系列都有一个斜率值。所定义的线的方程式如下:

Y = X1 Slope1 + X2 Slope2 + … + Xn * SlopeN + Intercept

因为我们只用了一个X轴参数的值,因此我们只需要Slope1。在编写公式时,您知道需要使用多少斜率参数,因为它对应于提供给LINESTX的X轴值的数量。

在DAX中创建线性回归现在比以前容易多了(也可以通过计算组),您只需注意正确使用变量,这些变量还需要避免多次评估同一个LINESTX函数。

转载请注明出处!


如果您想深入学习微软Power BI,欢迎登录网易云课堂试听学习我们的“从Excel到Power BI数据分析可视化”系列课程。或者关注我们的公众号(PowerPivot工坊)后猛戳”在线学习”。



长按下方二维码关注“Power Pivot工坊”获取更多微软Power BI、PowerPivot相关文章、资讯,欢迎小伙伴儿们转发分享~


Power Pivot工坊