幂指数的多元线性回归

本质:

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,您将无法找到非常好的方法。

因此,我建议的方法将忽略您关于以下方面的请求:

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

但是,我的回答将满足您关于以下方面的要求:

一个好的答案应该概括到3列以上(可能是 使用索引作为值而不是 超过列标题)。

我们开始吧:

我在一个使用逗号作为十进制分隔符的系统上,因此我将使用以下数据源(如果将数字直接复制到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的零开始(出于再现性目的),我使用 Enter Data :

现在,转到 Edit Queries > Edit Queries 并检查您是否有:

为了在分析中包含的列数方面保持灵活性,我发现最好删除 日期 柱这不会对回归结果产生影响。只需右键单击日期列并选择 Remove :

请注意,这将在下面添加一个新步骤 Query Settings > Applied Steps >:

这就是您可以编辑我们将要使用的几行R代码的地方。现在,转到 转换(>);运行R脚本 要打开此窗口,请执行以下操作:

注意这条线 # 'dataset' holds the input data for this script . 谢天谢地,您的问题只涉及一个输入表,所以事情不会变得太复杂(对于多个输入表,请查看 this post ). 这个 数据集 变量是表单数据的变量。R中的框架是好的(唯一的…)进一步分析的起点。

插入以下脚本:

型号<-lm(管理器~,数据集)
df<-数据框架(coef(模型))
名称(df)[名称(df)=“系数模型”。]<-“系数”
df['variables']<-一行名称(df)

点击 OK ,如果一切顺利,您应该以以下方式结束:

点击 Table ,您将看到:

在下面 Applied Steps 你会发现 Run R Script 已插入步骤。单击星形(齿轮?)在右侧进行编辑,或单击 df 格式化输出表。

就是这样! 对于 编辑查询 至少部分。

点击 Home > Close & Apply 要返回Power BI报告部分,请确认下面有一个新表 Visualizations > Fields :

插入表格或矩阵并激活系数和变量以获得:

我希望这就是你要找的!

现在,有关R脚本的一些详细信息:

只要可能,我将避免使用大量不同的R库。这样可以降低依赖性问题的风险。

功能 lm() 处理回归分析。就解释变量的数量而言,获得所需灵活性的关键在于 Manager ~ . , dataset 部分这只是说对 经理 数据帧中的变量 dataset ,并使用所有剩余列 ~ . 作为解释变量。这个 coef(model) 部分从估计模型中提取系数值。结果是一个以变量名作为行名的数据帧。最后一行只是将这些名称添加到数据帧本身。

因为没有等效的或方便的替代品 LINEST Power BI中的函数(我相信您在发布问题之前已经做了足够的研究),任何尝试都意味着在Power Query/m中重写整个函数,对于简单线性回归来说,这已经不是那么“简单”,更不用说多个变量了。

与其(重新)发明轮子,它不可避免地要容易得多(一行代码…)在Power BI中使用R脚本完成此操作。

考虑到我之前没有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 是内置的 linear model function 从R开始 tidy 函数随 broom 程序包,其中 tidies up the output and output a data frame 对于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"""

我已经知道了如何具体地对三个变量执行此操作,但这种方法根本不能放大或缩小到更多或更少的变量。

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值与这些值略有不同,因为它们是根据未经四舍五入的回报而不是问题中提供的四舍五入的回报计算的。

我引用了 this document 对于计算设置和Mathematica解决系统: