PowerBI 多版本實際預測綜合分析 第一彈

• 2019 年 10 月 6 日
• 筆記

概述

預測，在商業中，是一個永恆的話題。PowerBI對預測的支援首先要承認是很有限的。對於非常多的企業，從計劃管理的角度，會有這樣的情況：

• 依賴人的經驗輔助計算得到某些指標的預測。（如：銷售額，員工數，項目完成度等）
• 每年做全年預測。
  • 預測是如何進行的，可以由業務執行者完成。
  • 常常依賴於經驗，也就是拍腦袋或梳頭髮。
• 允許預測是帶有偏差的。
  • 企業高層決策深知這是常態。
  • 甚至偏差是可以被當做目標的。
  • 因此預測往往會是保守的，使得實際總是會更加理想。
• 允許預測可以每個月進行調整。
• 隨著時間的推移，每月的調整預測將更加準確。
• 直至整年結束。

上述流程幾乎是非常成熟的，從設計來看，這是一種非常好的模式，因為預測與實際的呈現與預測的過程是獨立的，也就是說，預測與實際的對比根本不Care預測的過程是瞎猜的，AI的，計算的還是如何得到的。

例如：

如上所示，含義如下：

• 2019年1月 分別預測了個月的業務指標，並以紅色顯示。
• 2019年2月 已知了1月的實際，填入並以綠色顯示；重新調整對隨後月份的預測，並以紅色顯示。
• 依次類推。

在現實中，可能需要的內容要比這樣的顯示更加複雜，但很多複雜可以由巧妙的設計來擴展。我們一起來研究這裡的設計應該具有的特點。

建模中的核心思想

上述展示了模型的核心部分。所有現實的各種複雜性都將基於這個不變的結構進行擴展。

值得強調的是：

• 不變的部分：每個月知道小於本月的實際，且不再改變；預測大於等於本月的部分。
• 擴展的部分：具體的指標是可以擴展的。

因此，實際的情況可能是：

當用戶選擇不同指標以及預測的子項時，該矩陣會自動顯示出相應的結果。這體現了BI的切片效應。

數據準備

值得強調的是，該模型的實際數據與預測數據來自外部，實際數據可以直接導出，而預測數據是怎麼得到的，這裡並不關心，這點非常重要。

在某些企業，預測是可以交給經驗豐富的專業人員進行的；在某些企業，預測又是可以交給某精確複雜的計算流程實現的（如：通過R的某些特別適合的演算法等）。

在這裡我們只關心結果，並在結果的基礎上供決策人員進一步分析。

準備的數據至少由兩部分構成：

• 實際數據
• 預測數據

而它們都必須符合一個特點：每月更新版本。

這裡模擬了它們可能的存放結構：

其中，實際數據：

類似的，預測數據：

以201904版本為例，對於實際數據，其結構為：

其特點是：對於某月版本，只能知道小於該月版本的實際數據。

以201904版本為例，類似的，對於預測數據，其機結構為：

其特點是：對於某月版本，只能填寫大於等於該月版本的預測數據。

因此，在數據處理中，需要：

• 分離出不同的指標。
• 合併整個實際數據。
• 合併整個預測數據。

這部分在傳統上，可能需要IT來完成，而在自助商業智慧分析的體系下，可以藉助PowerBI的查詢編輯（又叫：PowerQuery）由業務分析人員自行完成，如下：

這裡給出了一個標準的參考。

大家可以自己模擬數據並根據學到的PowerQuery知識來嘗試，關於這部分系統的能力教學已經收錄在我們的《PowerBI自助商業智慧分析系列影片教程》中，此處不再贅述。

對於處理好的數據，如下所示：

命名，體現了極高的專業性，例如：YMDate表明這是一個日期類型，目的是為了與日期表進行關聯，如果需要發生日期維度的計算，可以直接利用日期（時間）智慧函數的便捷性。而YM二字體現該列的實際粒度在月的級別，使用日期級別是錯誤的。 合理的命名，可以給後期的使用帶來重大的便利。 通過查看一個人的命名，便知道他對PowerBI或DAX理解的級別。

數據模型

這是一個典型的多事實（明細）表案例，如下：

在這個模型中，由三大非常重要的維度：

• 日期
• 版本
• 屬性

這決定了用戶可以在某版本來觀察某些屬性在某些日期的實際與預測綜合表現。

度量值

以 銷售額 和 利潤 為例，來看度量值的寫法：

KPI.Actual.Base =  VAR vKPIName = SELECTEDVALUE( KPIName[KPIName] , "Sales" )  RETURN CALCULATE( SUM( DataActual[Value] ) , DataActual[KPIName] = vKPIName )    KPI.Forecast.Base =  VAR vKPIName = SELECTEDVALUE( KPIName[KPIName] , "Sales" )  RETURN CALCULATE( SUM( DataForecast[Value] ) , DataForecast[KPIName] = vKPIName )    以及：    KPI.Base = [KPI.Actual.Base] + [KPI.Forecast.Base]    以及：    KPI = IF( ISFILTERED( 'Version'[Version] ) , [KPI.Base] )  

由於前面的設計，讓這裡的度量值編寫非常具有技巧性：

可以看出，這裡構建了具有層次性的度量值體系，它們的特點在於：

• KPI，處理版本化後的KPI.Base；
• KPI.Base，計算實際與預測結合的綜合效果；
• KPI.ActualBase，計算實際；
• KPI.ForecastBase，計算預測。

Base一詞，語義為基礎，它預示著這是一個不該直接使用的度量值，而是為另外的度量值提供了基礎。

這裡體現了一個重要的設計思想：用層次結構解除內部依賴結構。

當用戶的需求開始發生變化時，您會發現這樣的結構可以讓我們靈活地工作在不同的位置，可以應對業務的變化。

值得強調的是：

KPI.Base = [KPI.Actual.Base] + [KPI.Forecast.Base]  

在這裡有一個非常驚艷的計算，KPI居然直接可以由實際與預測相加。這基於下面的優化（這是為了讓您遇到問題時，回來第N次查看時可以得到的感悟）：

• 在數據準備中，實際數據我們需要用戶填寫積累更新的每月數據，而不是僅僅填寫當月數據。這樣，我們取不同版本計算時，都可以確保該版本下，需要有實際數據的月份都可以有實際數據，而不用去考慮低版本中的實際數據。
• 在數據準備中，我們明確發現有實際則預測無效；有預測則實際無效；因此，綜合表現恰好為實際與預測的和。這種直接加和的巧合，不但簡化的計算，還可以免去IF邏輯，大幅優化了性能。

度量值的染色

我們知道PowerBI給出了設置顏色的功能，但在很多高級的專業設計中，我們需要的是DAX驅動的可視化。這裡直接使用度量值來製備染色方法。

-- 當用戶選擇版本和日期時，我們判斷該版本下在該日期段是否已經有了實際數據    Version.IsUpdate =  VAR vYMNumber = SELECTEDVALUE( 'Calendar'[Year] ) * 100 + SELECTEDVALUE( 'Calendar'[Month] )  VAR vVersion = SELECTEDVALUE( 'Version'[Version] )  RETURN vYMNumber < vVersion    -- 對實際與預測進行染色    KPI.Color =  IF( [Version.IsUpdate] , "#348640"  , "#B10113" )  

總結

這是一個非常通用的業務模型，它為企業在月粒度管理企業計劃與實際的所有需求提供了核心模型；這裡作為該模型的第一課，希望大家可以自行實踐。

在很多大型傳統企業，尤其是外企，這是一種幾乎通用的方法，在PowerBI中的實現可以讓人眼前一亮，為您在外企的晉陞提供專業助力。

在企業的實際運用中，可能會基於此提出更多需求，例如：

• 在一個月同時顯示不同指標；
• 計算前後版本的估算差異；
• 計算季度粒度以及年度粒度的完成性；
• 動態篩選某些維度來觀察。

這些可能性我們將在後續文章進一步展開，歡迎您需要關注。

案例模型及模擬數據已經共享至訂閱會員區，請大家自行獲取學習。

歡迎交流