用 LINEST、LINESTX 函數在 Power BI 實現線性回歸

在 Power BI 2023 年二月的更新中，釋出了兩款最新函數：LINEST 與 LINESX 用來實現線性回歸。

在這篇文章中，我將帶你分別認識這兩個函數，以及利用一個簡單範例介紹函數的用法。

先備知識

由於本文會提及一些相關概念，在正式開始以前，請先確認你已經知道以下幾個名詞與代表的意涵。

  • Iterator Function
  • SUMMARIZE

LINEST 函數是什麼？

背後使用最小平方法針對一資料集進行回歸，進而得出最適合該組資料的線。函數會回傳一描述該線的表格。該直線的方程式為：y = Slope1*x1 + Slope2*x2 + ... + Intercept。

定義

根據微軟的官方，函數定義如下：

公式若超出邊界可以往右滑動唷👉

LINEST ( <columnY>, <columnX1> [, <columnX2>, [...] ] [, <const>] )

參數

參數 說明
columnY
  • 已知的 y 值的資料行。必須為純量數值類型。
columnXn
  • 已知的 x 值的資料行。必須為純量數值類型。必須至少提供一組 x 資料行。
const
  • （可選） TRUE / FALSE 值，指定是否強制截距等於 0。 如果為 TRUE 或省略，則正常計算截距值； 如果為 FALSE，則截距值設置為零。

回傳值

函數會回傳一僅含有單列的資料表。該資料表的含有許多不同資料行，描述該線性回歸的結果：

  • Slope1, Slope2, …, SlopeN：每個 x 值所對應的斜率。
  • Intercept：截距。
  • StandardErrorSlope1, StandardErrorSlope2, …, StandardErrorSlopeN：Slope1, Slope2, …, SlopeN 的標準誤差值。
  • StandardErrorIntercept：截距的標準誤差值。
  • CoefficientOfDetermination：決定係數，或稱 R^2。比較實際與估計的 y 值。數值範圍為 0 到 1 之間，數值越接近 1，數據間的相依性越高。
  • StandardError：估計值 y 的標準誤差。
  • FStatistic：F statistic 或 F-observed value。用來決定觀察到的因變量和自變量之間的關係是否偶然發生。
  • DegreesOfFreedom：自由度。使用此值可以幫助在統計表中找到 F-critical 值，並確定模型的信賴區間。
  • RegressionSumOfSquares：回歸平方和。
  • ResidualSumOfSquares：殘差平方和。

LINESTX 函數是什麼？

背後使用最小平方法針對一資料集進行回歸，進而得出最適合該組資料的線。函數會回傳一描述該線的表格。該直線的方程式為：y = Slope1*x1 + Slope2*x2 + ... + Intercept。

看到這邊你可能會覺得：「咦？這不是跟前一個 LINEST 一樣嗎？」

如果你仔細看就會發現，LINESTX 比起 LINEST 更多了一個 x 在函數尾端。在 Power BI 中，這代表這函數是一個 Iterator Function。

定義

根據微軟的官方，函數定義如下：

公式若超出邊界可以往右滑動唷👉

LINESTX ( <table>, <expressionY>, <expressionX1> [, <expressionX2>, [...] ] [, <const>] )

參數

參數 說明
table
  • 用來計算 expression 的資料表。
expressionY
  • 針對資料表的每一列計算的表達式，以獲得已知的 y 值。必須為純量數值類型。
expressionXn
  • 針對資料表的每一列計算的表達式，以獲得已知的 x 值。必須為純量數值類型。必須至少提供一組 x 資料行。
const
  • （可選） TRUE / FALSE 值，指定是否強制截距等於 0。 如果為 TRUE 或省略，則正常計算截距值； 如果為 FALSE，則截距值設置為零。

回傳值

函數會回傳一僅含有單列的資料表。該資料表的含有許多不同資料行，描述該線性回歸的結果：

  • Slope1, Slope2, …, SlopeN：每個 x 值所對應的斜率。
  • Intercept：截距。
  • StandardErrorSlope1, StandardErrorSlope2, …, StandardErrorSlopeN：Slope1, Slope2, …, SlopeN 的標準誤差值。
  • StandardErrorIntercept：截距的標準誤差值。
  • CoefficientOfDetermination：決定係數，或稱 R^2。比較實際與估計的 y 值。數值範圍為 0 到 1 之間，數值越接近 1，數據間的相依性越高。
  • StandardError：估計值 y 的標準誤差。
  • FStatistic：F statistic 或 F-observed value。用來決定觀察到的因變量和自變量之間的關係是否偶然發生。
  • DegreesOfFreedom：自由度。使用此值可以幫助在統計表中找到 F-critical 值，並確定模型的信賴區間。
  • RegressionSumOfSquares：回歸平方和。
  • ResidualSumOfSquares：殘差平方和。

利用 LINESTX 執行線性回歸預測

此處我們將利用 LINESTX 來執行線性回歸預測。

如果你也想一同跟著動手實作的話，歡迎下載我為你準備好的範例檔。

想要自己試試看嗎？

​

如果你對文章內使用的 pbix 檔案有興趣

這裡可以下載👇👇👇

    Built with ConvertKit

    步驟一、問題情境

    在已知的資料中，藉由建立兩個量值：[Total Cost] 與 [Total Sales] 我們可以很容易建立針對不同商品類別的成本與銷售額。

    不同產品類別的成本與銷售額
    不同產品類別的成本與銷售額

    倘若，今天公司想要開拓新的產品線，想要新增以下三種不同的產品的話，該怎麼在已知成本的狀況下，預測銷售額呢？

    • Hiking Gear
    • Camping Gear
    • Diving Gear

    首先，我們可以新增以下資料表，並且保持其在資料模型中無任何關聯。

    新產品類別與成本估計
    新產品類別與成本估計
    資料模型

    步驟二、線性回歸模型

    接下來，先新增一計算資料表，看看我們的線性回歸模型長什麼樣子？

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    Expected Sales Model =
    VAR  salesCostByCategory =
         SUMMARIZE  (
            Sales,
            ProductCategory[ProductCategory],
             “@Total Sales”, [Total Sales],
             “@Total Cost”, [Total Cost]
         )
    VAR  predictedSalesModel =
         LINESTX  (  salesCostByCategory, [@Total Sales], [@Total Cost]  )
    RETURN
         predictedSalesModel
    LINEST
    線性回歸模型回傳的模型相關資訊

    從上圖可以發現，針對虛擬資料表 salesCostByCategory 內的 @Total Sales 與 @Total Cost 的線性回歸結果都被記錄在這張表中。

    接下來就是利用表中的斜率 Slope1 與截距 Intercept 進行計算。

    步驟三、利用模型預測

    新增一量值，寫法基本上跟上面差不多，需要多引入在步驟一的 Estimated Cost 乘上斜率加上截距就是我們要的結果。（Predicted Sales = Slop1 * Estimated Cost + Intercept）

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    Expected Sales =
    VAR  salesCostByCategory =
         SUMMARIZE  (
            Sales,
            ProductCategory[ProductCategory],
             “@Total Sales”, [Total Sales],
             “@Total Cost”, [Total Cost]
         )
    VAR  predictedSalesModel =
         LINESTX  (  salesCostByCategory, [@Total Sales], [@Total Cost]  )
    VAR  estimatedCost =
         SELECTEDVALUE  ( ‘New Categories'[Estimated Cost]  )
    RETURN
         SELECTCOLUMNS  (  predictedSalesModel, [Slope1]  ) *  estimatedCost
            +  SELECTCOLUMNS  (  predictedSalesModel, [Intercept]  )

    將量值寫完以後，用資料表視覺效果來視覺化最後的結果吧！

    新產品類別之預測銷售額
    新產品類別之預測銷售額

    結語

    LINEST 與 LINESTX 讓開發者可以在 Power BI 內實現線性回歸。兩者函數的差異是 LINEST 需要以資料行為參數；而 LINESTX 則是資料表與 expression 為參數。

    文中亦利用一個範例，簡單說明 LINESTX 的用法。儘管在真實的情境中，一定不會如此單純，僅有一個變數影響最後的銷售額。可能還會有其他因子（x2、x3．．．）。但這文章的目的是藉由簡單的範例讓閱讀到這的你可以了解兩個函數的定義與使用方式。

    對於這兩個函數你有什麼想法或是使用情境嗎？歡迎讓我知道唷！

    發佈留言

    發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *