Power BI中的多元线性回归

Question

假设我有一组收益数据，想要计算它们相对于不同市场指数的beta值。为了举一个具体的例子，让我们使用以下数据集，在名为Returns的表格中呈现：

  Date       Equity  Duration  Credit  Manager
-----------------------------------------------
01/31/2017   2.907%   0.226%   1.240%   1.78%
02/28/2017   2.513%   0.493%   1.120%   3.88%
03/31/2017   1.346%  -0.046%  -0.250%   0.13%
04/30/2017   1.612%   0.695%   0.620%   1.04%
05/31/2017   2.209%   0.653%   0.480%   1.40%
06/30/2017   0.796%  -0.162%   0.350%   0.63%
07/31/2017   2.733%   0.167%   0.830%   2.06%
08/31/2017   0.401%   1.083%  -0.670%   0.29%
09/30/2017   1.880%  -0.857%   1.430%   2.04%
10/31/2017   2.151%  -0.121%   0.510%   2.33%
11/30/2017   2.020%  -0.137%  -0.020%   3.06%
12/31/2017   1.454%   0.309%   0.230%   1.28%

现在在Excel中，我可以使用LINEST函数来获取beta值：

= LINEST(Returns[Manager], Returns[[Equity]:[Credit]], TRUE, TRUE)

它会输出一个类似于这样的数组：

0.077250253 -0.184974002  0.961578127 -0.001063971
0.707796954  0.60202895   0.540811546  0.008257129
0.50202386   0.009166729  #N/A         #N/A
2.688342242  8            #N/A         #N/A
0.000677695  0.000672231  #N/A         #N/A

这些beta值位于顶行，使用它们可以给出以下线性估计：

Manager = 0.962 * Equity - 0.185 * Duration + 0.077 * Credit - 0.001

问题是如何在Power BI中使用DAX获取这些值（最好不用编写自定义的R脚本）？

---

对于针对单列的简单线性回归，我可以返回到数学定义并编写类似于此帖子中给出的最小二乘实现。

然而，当涉及更多列时（我需要能够处理多达12列，但不总是相同数量），这很快变得混乱，我希望有更好的方法。

- Alexis Olson

Answer 1

要点：

DAX不是最好的选择。使用Home > Edit Queries，然后使用Transform > Run R Script。插入以下R代码片段来运行一个回归分析，使用表中所有可用变量：

model <- lm(Manager ~ . , dataset)
df<- data.frame(coef(model))
names(df)[names(df)=="coef.model."] <- "coefficients"
df['variables'] <- row.names(df)

将Manager编辑为其他可用变量名称以更改依赖变量。

---

细节：

好问题！为什么微软没有引入更灵活的解决方案超出了我的理解范围。但目前，如果不使用Power BI中的R，您将无法找到非常好的方法。

因此，我建议的方法将忽略您关于以下内容的请求：

问题是如何在Power BI中使用DAX获取这些值（最好不必编写自定义R脚本）？

但是，我的答案将满足您关于以下要求的要求：

一个好的答案应该适用于三列以上的数据（可能通过处理未旋转的数据表并将索引作为值而不是列标题来完成）。

我们开始吧：

---

我使用逗号作为小数分隔符的系统，因此我将使用以下内容作为数据源（如果您直接将数字复制到Power BI中，则列分隔符将无法保持。如果您首先将其粘贴到Excel中，然后再次复制并将其粘贴到Power BI中，则列将很好）：

Date    Equity  Duration    Credit  Manager
31.01.2017  2,907   0,226   1,24    1,78
28.02.2017  2,513   0,493   1,12    3,88
31.03.2017  1,346   -0,046  -0,25   0,13
30.04.2017  1,612   0,695   0,62    1,04
31.05.2017  2,209   0,653   0,48    1,4
30.06.2017  0,796   -0,162  0,35    0,63
31.07.2017  2,733   0,167   0,83    2,06
31.08.2017  0,401   1,083   -0,67   0,29
30.09.2017  1,88    -0,857  1,43    2,04
31.10.2017  2,151   -0,121  0,51    2,33
30.11.2017  2,02    -0,137  -0,02   3,06
31.12.2017  1,454   0,309   0,23    1,28

从头开始使用Power BI（为了可重复性），我使用输入数据插入数据：

现在，去 编辑查询 > 编辑查询 并检查你是否有这个：

为了保持分析中要包含的列的灵活性，最好删除日期列。这不会影响您的回归结果。只需右键单击日期列并选择删除：

请注意，这将在查询设置> 应用步骤下添加一个新步骤：

这就是您将能够编辑我们即将使用的几行R代码的地方。现在，进入转换> 运行R脚本以打开此窗口：

请注意行#'dataset' holds the input data for this script。幸运的是，您的问题仅涉及一个输入表，因此事情不会变得太复杂（对于多个输入表，请查看此帖子）。变量 dataset 是R中的data.frame形式的变量，并且是进一步分析的好（唯一的..）起点。

插入以下脚本：

model <- lm(Manager ~ . , dataset)
df<- data.frame(coef(model))
names(df)[names(df)=="coef.model."] <- "coefficients"
df['variables'] <- row.names(df)

点击OK，如果一切顺利，您应该会得到这个:

点击Table，您会得到这个:

在Applied Steps下，您将看到一个Run R Script步骤已经被插入。点击右侧的星星（齿轮?）进行编辑，或者点击df以格式化输出表格。

就是这样! 至少对于 Edit Queries 部分是这样。

点击Home > Close & Apply返回到 Power BI 报告部分，并验证您是否在Visualizations > Fields下有一个新表：

插入一个表或矩阵并激活系数和变量，您将得到这个：

希望这就是您要找的内容!

---

现在来介绍一些有关 R 脚本的细节:

只要可能，我会避免使用大量不同的 R 库。这样，您将减少依赖问题的风险。

函数lm()处理回归分析。获得关于自变量数量的所需灵活性的关键在于 Manager ~ . , dataset 部分。这只是说在数据框dataset中对Manager变量运行回归分析，并将所有其余列~ . 用作自变量。 coef（model）部分从估计模型中提取系数值。结果是一个带有变量名作为行名的数据框。最后一行只是将这些名称添加到数据框本身。

- vestland

---

这看起来相当不错。理想情况下，R脚本可以响应报告页面上的切片器和过滤器（因为我无法合理地预先计算我想要beta的所有可能组合），但我认为目前不可能将R用作度量的一部分（而且超出了我的问题范围）。 - Alexis Olson

完全同意Power BI应该有这些功能。但据我所知，我们还没有达到那个水平。关于他们添加新功能的速度，可能不会太久，我们就能做到你所描述的事情了。但现在，Power BI最擅长的一件事是可视化其他地方分析的结果。当涉及到预计算beta时，我建议您看看Python和statsmodels包。您将很快上手。我认为与DAX相比，R和Python要容易得多。 - vestland

如果你能够处理Power BI中的DAX，那么Python应该很容易。搜索Google Anaconda并从那里开始。Spyder或Visual Studio应该是一个不错的入门IDE。然后，你可以看一下我最近在这里发布的关于在表的子集上运行回归的内容：https://stackoverflow.com/questions/48624071/run-regression-analysis-on-multiple-subsets-of-pandas-columns-efficiently/48822748#48822748 你所需要的只是Python和一个文本文件。然后你就可以运行任何你想要的回归，甚至将结果存储在另一个文本文件中。 - vestland

甚至可以在 Power BI 中可视化结果，如果那仍然是您最喜欢的工具包。我猜现在我让它听起来比实际复杂了...但如果您对这些事情感兴趣，真的应该看一下。 - vestland

你有没有成功地实现这个建议？ - vestland

Answer 2

由于在Power BI中没有与LINEST函数等效或方便的替代方法（我相信你在发布问题之前已经做了足够的研究），任何尝试都意味着在Power Query/M中重新编写整个函数，即使是简单线性回归的情况，也不那么“简单”，更不用说多个变量了。

与其重新发明轮子，使用R脚本在Power BI中处理会更容易（只需要一行代码...）。

考虑到我之前没有R经验，这并不是一个坏选择。 经过几次搜索和尝试，我设法想出了以下解决方案:

# 'dataset' holds the input data for this script
# install.packages("broom") # uncomment to install if package does not exist
library(broom)

model <- lm(Manager ~ Equity + Duration + Credit, dataset)
model <- tidy(model)

lm 是 R 中内置的线性模型函数linear model function，tidy 函数随附于 broom 包中，它可以整理输出并输出一个数据框架以供 Power BI 使用。

有了列term和estimate，这就足以计算您想要的估算值。

以下是M查询供您参考：

let
    Source = Csv.Document(File.Contents("returns.csv"),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type text}, {"Equity", Percentage.Type}, {"Duration", Percentage.Type}, {"Credit", Percentage.Type}, {"Manager", Percentage.Type}}),
    #"Run R Script" = R.Execute("# 'dataset' holds the input data for this script#(lf)# install.packages(""broom"")#(lf)library(broom)#(lf)#(lf)model <- lm(Manager ~ Equity + Duration + Credit, dataset)#(lf)model <- tidy(model)",[dataset=#"Changed Type"]),
    #"""model""" = #"Run R Script"{[Name="model"]}[Value]
in
    #"""model"""

- Foxan Ng

Answer 3

我已经想出了如何针对三个变量进行操作，但这种方法无法适用于更多或更少的变量。

Regression = 
VAR ShortNames =
    SELECTCOLUMNS (
        Returns,
        "A", [Equity],
        "D", [Duration],
        "C", [Credit],
        "Y", [Manager]
    )
VAR n = COUNTROWS ( ShortNames )

VAR A = SUMX ( ShortNames, [A] )
VAR D = SUMX ( ShortNames, [D] )
VAR C = SUMX ( ShortNames, [C] )
VAR Y = SUMX ( ShortNames, [Y] )

VAR AA = SUMX ( ShortNames, [A] * [A] ) - A * A / n
VAR DD = SUMX ( ShortNames, [D] * [D] ) - D * D / n
VAR CC = SUMX ( ShortNames, [C] * [C] ) - C * C / n

VAR AD = SUMX ( ShortNames, [A] * [D] ) - A * D / n
VAR AC = SUMX ( ShortNames, [A] * [C] ) - A * C / n
VAR DC = SUMX ( ShortNames, [D] * [C] ) - D * C / n

VAR AY = SUMX ( ShortNames, [A] * [Y] ) - A * Y / n
VAR DY = SUMX ( ShortNames, [D] * [Y] ) - D * Y / n
VAR CY = SUMX ( ShortNames, [C] * [Y] ) - C * Y / n

VAR BetaA =
    DIVIDE (
        AY*DC*DC - AD*CY*DC - AY*CC*DD + AC*CY*DD + AD*CC*DY - AC*DC*DY,
        AD*CC*AD - AC*DC*AD - AD*AC*DC + AA*DC*DC + AC*AC*DD - AA*CC*DD
    )
VAR BetaD =
    DIVIDE (
        AY*CC*AD - AC*CY*AD - AY*AC*DC + AA*CY*DC + AC*AC*DY - AA*CC*DY,
        AD*CC*AD - AC*DC*AD - AD*AC*DC + AA*DC*DC + AC*AC*DD - AA*CC*DD
    )
VAR BetaC =
    DIVIDE (
      - AY*DC*AD + AD*CY*AD + AY*AC*DD - AA*CY*DD - AD*AC*DY + AA*DC*DY,
        AD*CC*AD - AC*DC*AD - AD*AC*DC + AA*DC*DC + AC*AC*DD - AA*CC*DD
    )
VAR Intercept =
    AVERAGEX ( ShortNames, [Y] )
        - AVERAGEX ( ShortNames, [A] ) * BetaA
        - AVERAGEX ( ShortNames, [D] ) * BetaD
        - AVERAGEX ( ShortNames, [C] ) * BetaC
RETURN
        { BetaA, BetaD, BetaC, Intercept }

这是一个计算表格，返回指定的回归系数：

这些数字与提供的数据的LINEST输出相匹配。

注意：我在问题中引用的LINEST值与这些值略有不同，因为它们是从未舍入的返回值计算而来，而不是问题中提供的舍入返回值。

---

我参考了这份文档来进行计算设置，并使用Mathematica来解决该系统：

- Alexis Olson