Multiple Linear Regression in Power BI

Question

Suppose I have a set of returns and I want to calculate its beta values ​​depending on different market indices. Let's use the following dataset in a table named Returnsto have a concrete example:

  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%

Now in Excel, I can simply use the function LINESTto get the beta values:

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

He spits out an array that looks like this:

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

Betas are in the top row, and using them gives me the following linear estimate:

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

The question is how to get these values ​​in Power BI using DAX (preferably without the need to write your own R-script)?

---

, .

, ( 12 , ), , , .

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 , .

---

:

! Microsoft , . R Power BI.

:

, Power BI DAX ( 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, . Transform > Run R Script :

, # 'dataset' holds the input data for this script. , , ( ). data.frame R (..) .

:

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 . (gear?) , , df .

! Edit Queries, .

" Home > Close & Apply Power BI, , " Visualizations > Fields:

, :

, , !

---

R:

, R. .

lm() . Manager ~., dataset Manager ~., dataset Manager ~., dataset. , Manager dataset dataset ~. . coef(model) . . .

Answer 2

Power BI LINEST ( , ), Power Query/M, "" , .

, , ( ..), R script 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, 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"""

Answer 3

Hi ~ Here you can find a way to complete multiple linear regression using DAX https://www.linkedin.com/pulse/building-optimizing-multiple-linear-regression-powerbi-davis-zhang