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

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

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

【🆓 免費資源】從零開始，理解 Power BI 與 DAX 函數底層邏輯 🚀

如果你想：

擺脫用 Excel 製作重複性報表的無限輪迴
克服用 Excel 只能存一百萬列資料之限制
徹底理解 Power BI 與 DAX 函數底層邏輯
了解學習 Power BI 在就業市場的競爭力

歡迎點擊下方按鈕，報名免費課程。
我們還會送你一套《DAX 函數從零到壹學習藍圖》！萃取數十個常用函數，幫助你學習有方向！

立即報名免費課程，領取藍圖！

—— Stark：雖然是免費課程，但內容絕對不馬虎，推薦你找時間好好看完！

內容目錄

先備知識

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

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 函數是什麼？

看到這邊你可能會覺得：「咦？這不是跟前一個 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 來執行線性回歸預測。

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

步驟一、問題情境

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

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

Hiking Gear
Camping Gear
Diving Gear

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

步驟二、線性回歸模型

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

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

線性回歸模型回傳的模型相關資訊

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

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

步驟三、利用模型預測

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

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．．．）。但這文章的目的是藉由簡單的範例讓閱讀到這的你可以了解兩個函數的定義與使用方式。

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

關於 Stark

是一名在科技業賣肝的軟體工程師，協助企業資料視覺化。

致力於分享 Power BI 知識與技術，讓資料擁有無限可能。

關注我的 Instagram 獲得更即時資訊：Stark@I Master Power BI。

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

先備知識

LINEST 函數是什麼？

定義

參數

回傳值

LINESTX 函數是什麼？

定義

參數

回傳值