在 Power BI 2023 年二月的更新中，釋出了兩款最新函數：LINEST 與 LINESX 用來實現線性回歸。
在這篇文章中，我將帶你分別認識這兩個函數，以及利用一個簡單範例介紹函數的用法。
先備知識
由於本文會提及一些相關概念，在正式開始以前，請先確認你已經知道以下幾個名詞與代表的意涵。
- Iterator Function
SUMMARIZE
LINEST 函數是什麼？
背後使用最小平方法針對一資料集進行回歸，進而得出最適合該組資料的線。函數會回傳一描述該線的表格。該直線的方程式為：y = Slope1*x1 + Slope2*x2 + ... + Intercept。
定義
根據微軟的官方，函數定義如下：
公式若超出邊界可以往右滑動唷👉
LINEST
( <columnY>, <columnX1> [, <columnX2>, [...] ] [, <const>] )
參數
| 參數 | 說明 |
|---|---|
| columnY |
|
| columnXn |
|
| const |
|
回傳值
函數會回傳一僅含有單列的資料表。該資料表的含有許多不同資料行，描述該線性回歸的結果：
- 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：殘差平方和。
免費 Power BI 培訓
先看懂重複報表怎麼自動化，再把 Power BI 與 DAX 學對
如果你還在 Excel 裡反覆整理資料、每次更新報表都要重新核對，或看了很多 DAX 公式卻不知道為什麼算得對，這套免費培訓會帶你從資料整理、資料模型到計算邏輯，建立一條能真正帶回工作使用的學習順序。
從重複整理、資料模型到 DAX 計算，先建立能帶回工作使用的學習順序。
- 把重複整理改成可更新的 Power BI 流程
- 看懂資料模型與 DAX 為什麼算得對
- 用 10 日練習，把觀念帶回工作
Stark 實戰教學
- 免費培訓影片
- 10 日實作練習
- DAX 學習藍圖
LINESTX 函數是什麼？
背後使用最小平方法針對一資料集進行回歸，進而得出最適合該組資料的線。函數會回傳一描述該線的表格。該直線的方程式為：y = Slope1*x1 + Slope2*x2 + ... + Intercept。
看到這邊你可能會覺得：「咦？這不是跟前一個 LINEST 一樣嗎？」
如果你仔細看就會發現，LINESTX 比起 LINEST 更多了一個 x 在函數尾端。在 Power BI 中，這代表這函數是一個 Iterator Function。
定義
根據微軟的官方，函數定義如下：
公式若超出邊界可以往右滑動唷👉
LINESTX
( <table>, <expressionY>, <expressionX1> [, <expressionX2>, [...] ] [, <const>] )
參數
| 參數 | 說明 |
|---|---|
| table |
|
| expressionY |
|
| expressionXn |
|
| const |
|
回傳值
函數會回傳一僅含有單列的資料表。該資料表的含有許多不同資料行，描述該線性回歸的結果：
- 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
首先，我們可以新增以下資料表，並且保持其在資料模型中無任何關聯。
步驟二、線性回歸模型
接下來，先新增一計算資料表，看看我們的線性回歸模型長什麼樣子？
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）
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．．．）。但這文章的目的是藉由簡單的範例讓閱讀到這的你可以了解兩個函數的定義與使用方式。
對於這兩個函數你有什麼想法或是使用情境嗎？歡迎讓我知道唷！
留言