Множественная линейная регрессия в Power BI

Суть:

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 шаг. Щелкните звездочку (шестеренку?) Справа, чтобы отредактировать ее, или щелкните df, чтобы отформатировать таблицу вывода.

Вот и все! По крайней мере, в части Редактировать запросы.

Щелкните Home > Close & Apply, чтобы вернуться в раздел отчетов Power BI и убедиться, что у вас есть новая таблица в Visualizations > Fields:

Вставьте таблицу или матрицу и активируйте коэффициенты и переменные, чтобы получить это:

Надеюсь, это то, что вы искали!

Теперь некоторые подробности о сценарии R:

Насколько это возможно, я бы избегал использования множества различных библиотек R. Таким образом вы снизите риск возникновения проблем с зависимостями.

Функция lm() обрабатывает регрессионный анализ. Ключ к достижению требуемой гибкости в отношении количества объясняющих переменных лежит в части Manager ~ . , dataset. Это просто говорит о том, что нужно выполнить регрессионный анализ Manager переменной в фрейме данных dataset и использовать все оставшиеся столбцы ~ . в качестве независимых переменных. Часть coef(model) извлекает значения коэффициентов из оценочной модели. Результатом является фрейм данных с именами переменных как именами строк. Последняя строка просто добавляет эти имена в сам фрейм данных.

Поскольку в 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, а функция 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"""

Я выяснил, как это сделать специально для трех переменных, но этот подход вообще не масштабируется до большего или меньшего количества переменных.

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 }

Это вычисляемая таблица, которая возвращает указанные коэффициенты регрессии:

Эти числа соответствуют выходным данным ЛИНЕЙН для предоставленных данных.

Примечание. Значения ЛИНЕЙН, которые я привел в вопросе, немного отличаются от тезисов, поскольку они были рассчитаны на основе неокругленных результатов, а не округленных результатов, представленных в вопросе.

Я сослался на этот документ для настройки расчетов и Mathematica для решить систему: