Data platform & Analytics

在上一篇的時序分析，我們學會了要如何處理，累積一段時間的資訊的分析與預測。接著，本文將會介紹，除了如何快速地達成，企業業常用的同期成長、迄今總計、移動平均…等分析。也會介紹要如何回應企業常見的複複商業邏輯？與如何處理畫面中資料細節的變化？並著重在視覺化的表現。
為何我會說「快速」？因為，早期在 Power BI上要實作「與去年相比YOY」是需要花上3~5分鐘，除了要撰寫2個以上的量值，還需組出一個"今年量值"減掉"去年量值"的 DAX語法邏輯來。雖然寫起來是比 Excel 快，但在回應這個需求時，還是不夠直覺、不夠神速！所幸，微軟的專長就是在開發各種精靈 Wizard，讓你透過下一步、下一步就能達成你的需求。很快地，當這個功能在二年前左右被 RD team開發出來時，真的就不到 30秒就能完成(待會會介紹)。
另外，有一個大前題需要先溝通！雖然我沒有辦法在一篇講完，要如何設計出一個可以滿足多個部門、多種角色(工作職掌)的全方位報表…但是，我們需要先建立起一個概念，BI 工具是能透過 Filter篩選器、Slicer切片器、Context上下文、DAX(PowerBI)或LOD(Tableau)、畫面中其他視覺化元器的互相篩選(互動)…來實作出高度視覺化的自助化分析，而我們會需要涉獵到資料顆粒、不同物件的篩選先後順序、函數運算、資料儲存設計…等範疇，才有辦法駕馭好這個工具的。

一、原理：
假設，總經理拿了一個以表格來呈現的一萬列資料，十年的歷史資料報表，要你在上面增加 YOY的年度成長分析。當你拿到素材之後，想要先了解全盤的狀況。當你好奇地用 Slicer 選了五年的時間，篩掉了一半的筆數；在圓餅圖上面點了男性，又篩掉了一半的筆數；在地圖上你點了亞洲，先不管表格中的資料被篩掉的數量或比例是否正確？至少有一張Top 5的直條圖，畫面中只剩下Top 3你該如何在主管問你之前主動修好呢？還有 Year over Year的年度成長又該如何實現呢？
首先，你需要先了解下面二張圖，研究一下系統背面運作的原理或機轉，以免當使用者做了一些篩選，不如你預期時，才不會驚慌失措！又或者因為你徹底地了解，是可以為你省下 try & error的時間，讓你專注在更重要的工作上…

在二大陣營中都能實現出端到端的解決方案，包含：資料來源、上下文、Dimension、Measure、表計算…等篩選，甚至是做到 Row level security，讓同一份報表內容，都能為不同的角色與授權，呈現出不同的資料。例如，總經理看到全公司，北區主管，只能看到北區資料，不是主管，看不到任何資料…

補充一下，在 PowerBI 是將資料源做關聯，有時候會加上 Group 來彙總大顆粒的資料，然後包含上下文 Context filter往下，一直到 Table calculation filter 都是交由 Syntax語法來決定一切。而 Tableau 的從上至下的原理一樣(雖然在關聯與 Group的作法有些細節上的差異)，只是它多開發了 GUI，除了上圖中灰色的 LOD 需要撰寫語法，其他用滑鼠選一選就可以完成了。受限於 Power BI有微軟眾多產品線與向下相容的包袱，它也只能用 DAX撐起全場，所幸它的語法有 70%都跟 Excel一樣，對於熟悉 Excel 的朋友比較沒有進入上的障礙…

二、同期比較：
以 Power BI來說，這個功能是歸屬在Quick measure 中時間智慧的分類，除了(月/季/年)的同期比較，還有(月/季/年)的累計至今，以及移動平均。它們的應用情境，分別是 Month over month change 比較鄰近的二個時間點的數值變化、Month to date total 從年初開始累積至當前的時間單位、Rolling average 一種可透過調整權重來消除短期波動，突出長期趨勢或周期的統計方式。
以下我們將以 Super market 資料集來實作，首先，要選 Matrix 元件，然後把 Order Date-Month, Order Date-Year, Sales 分別拖進 Rows, Columns, Values

接著，在上方的 Home 頁籤中找到 Quick measures，並且下拉 Calculation 在 Time intelligence 區域找到 year-over-year change，再從右邊的 Fields中把 Sales 銷售金額拖進左邊 Base value；把Order Date拖進左邊 Date

系統將會自動產生 Sales YoY%的量值

接著，再加新的 Measure 拖到 Matrix 元件中的 Values 屬性，如果你看到首年 2010的 YoY為空白，這是正常的！因為我們剛才有提到，它是由兩兩相鄰的做比較，所以當首年沒有前一年可以比較，只好為空白，往右看，其他年就正常了！

有了同期成長，主管的心中就會有一個 Base line，了解今年是否有比去年進步？該怎麼調度手上的資源或是調整優先順序…

同理，Running total迄今累計也是透過這個精靈，我們先把回到初始的單純年月銷售金額(我把YOY量值拉掉，畫面會比較清爽好講解)，另外，我想要把年月改成年季(在 Rows換一下 Dimension)，在展示上比較不呆板有變化。 

然後就能按下 Quick measures，當畫面中的左邊出現 Time intelligence大類時，把 Scroll bar 繼續往下拉，直至看到 Totals大類，你就能找到 Running total。接著，要把 Order Date-Quarter拖進 Field，以及把 Sales拖進 Base value，再按下 OK 

就會產生一個叫做 "Sales running total in Order Date"的新量值，由於我們要將它擺進 Matrix中與銷售金額做比較，所以先把它 Rename 成 "Running total" 會比較好排版，就能拖進 Matrix 的 Values中 

假設現在是Q3，有了迄今累計，主管的心中就會有一個 Whole picture，除了了解各個季度的績效，也能知道累積至Q3，距離最後1Q還有多少數字要交付？該怎麼調度手上的資源或是調整優先順序…甚至是接下來要進來的大單，要不要藏單到明年，呵…好像講太多了

---

至於在 Tableau 呢？首先，要將 Order Date-Year, Order Date-Month, Sales 分別拖進 Columns, Rows, Text 中，繪製出類似的表格呈現
 

然後，對著 Sales 做 Year over year growth 的表計算 

就能得到以下的結果 

然後再按著 Ctrl 將 Sales 拖進 Text 一次 

然後再把 Measure Names, Measure Values 分別拖進 Columns與 Text中，與微調 Measure Values的順序

大功告成

三、上下文篩選：
Top N是企業中常用的分析應用，但是你馬上就會遇到一個嚴重的「分母問題大挑戰」，需要有一個因地制宜的智慧篩選器。在第一版的報表中，你做了一個全產品線的 Top10給總經理，當他看完後，拿去問傢俱部門的主管為何當季賣的不好？然後傢俱部門要你幫他加一個篩選器可以看到他們部門的 Top10，結果加完篩選器竟然只看到 2個產品在報表中！主管就問你說，說好的 Top10怎麼縮水了？原因就是分母不一樣。舉例來說，請問在學校的段考成績中，全校前10名與全班前10名會是相同的人嗎？因為分母不一樣，所以當你切換至A/B/C/D/E不同的班級時，B班的第1名(較小的分母)，不在全校前10名(較大的分母)也是常會發生的。

接下來，我們要介紹在Tableau 要怎麼實作？基礎於畫面中已經有 Category & Product Name 與降冪排序的銷售金額，你只需要按住 Ctrl 將 Product Name 拖進 Filters 中

系統就會跳出精靈，當你切到 Top 頁籤時，它的預設值剛好是符合我們的情境的，可以直接按下 Ok

然後，我們再新增一個類別的篩選器，並且改成 Single value list 

當我們想要分析傢俱、辦公室用品、科技…三個類別，分別是哪些產品賣的比較好？畫面中分別會得到3個、0個、7個產品的結果

你可能會問，說好的 Top10怎麼縮水了？原因是全校前10名與全班前10有很大的可能，不是相同的10個人，所以當你切換ABC不同的班級，B班完全沒有全校前10名也是可能的。當你要解決這個問題，只要找到 Filters 對著 Category 按右鍵，並選擇 Add to context 

不管原來這個篩選條件上下的順序，一旦套用了 Add to context之後，它就會被置頂，並且會變成灰色。在底層中，原本的 "Top 10 by Sales" 「AND」"Category"的篩選條件，其中的邏輯運算元 AND 會被改成 OR，來回應使用者在某些情境的需求 

就能達到任意切換不同的類別，都有 Top 10的產品可供你來比較結果

接下來再講一個更進階地概念，在自助分析的世界中，你想提高使用者的自由度，就要準備足夠的資料量，但是很矛盾地你又會害怕使用者在資料的叢林中迷路，因此 Context filter 將會可以成為一座燈塔在你所製作的報表/儀表板，為使用者指引適當的方向，在下圖中是一家國際級的零售百貨業者，它的銷售儀表板，除了地圖，還有熱賣的產品與高獲利的客戶分析。

雖然這只是二個階層的設計(世界地圖=>產品 or 客戶)，但由於產品與客戶的資料太多，使用者很容易就在畫面中迷路了，所以我們可以設計一個由上(地圖)而下，再左(產品)至右(客戶)或是由右(產品)至左(客戶)的 Top 10 分析腳本，以利儀表板的閱讀者可以先挑選有興趣的國家，再看客戶，最後是產品。
首先，要準備 Top N filter 在產品與客戶報表中，先縮小範圍至 Top 10 (這個數值也可以設計成參數讓使用者來自訂)，接下來我們要在地圖元件中按下漏斗篩選的 icon，讓它可以與另外二個報表元件做連動。重點來了，在這二個報表你都會看到地圖篩選與 Top10 篩選，你該設定哪一個為 Context filter 呢？答案是上層者，地圖 => 產品/客戶。
當你想要貼心地為使用者設計一個由左至右，或是由右至左的分析，你要先建立一個 Dashboard action，然後再移到報表中，再設定新建的 Action 成為第二個 Context filter… 

四、LOD(Level of detail)：
承上，雖然Context 上下文篩選功能，能透過提供給使用者在報表中 Top N, Bottom N…等智慧篩選器，來解決畫面上的分母問題。可是，如果是畫面中沒有出現的，就必需透過 LOD (在 Power BI中是 DAX)語法來處理。換句話說，在不同的資料分析需求中，有時候會需要將特定的維度進行固定/包含/排除…的處理…

常見的應用情境，包含客戶下單量分析、逐年回購分析(柱狀圖)、地區佔比分析(假設你們公司在美洲已經獨大，主管想看摒除美洲後，在指定的五大洲發展的狀況)、新客戶獲得分析、各產品的銷售分析、依不同業務的平均大訂單分析、各產品的目標達成分析、訂單截止日分析、季度的回購分析(關聯矩陣 Correlation matrix clustering)、相關期間篩選分析(年初累積至今日的毛利「與去年相比」是常見的分析題目，但如果總經理想看比去年更遠的資料呢？)、使用者登入分析、在地圖中與任意國家做比較的分析(使用 Proportional brushing 技術)、客戶回購的年度頻率分析(折線圖)
**另外，請回頭看一下，在原理中的架構圖，LOD的優先順序還挺高的，不管你在畫面中是拖進、或是拉走任意 Dimension or Measure都不會改變它的結果

以下我們將以「各產品的銷售(毛利)分析」為例，為各位示範在 Tableau 要怎麼實作？但是在開始之前，我想要介紹一下「各產品」與「產品大類」，這二張報表，他們的差異在哪裡？
**提示一下，請觀察以下二張報表中的X軸，並且從毛利的「計算方式」來思考

報表一：產品類別的平均銷售與毛利分析

報表二：各產品的平均銷售與毛利分析

公佈答案，報表二是有包含產品類別的下一階(產品名稱)的濃縮結果，其次是先Sum再Avg與先Avg再Sum的差異。
當總經理想要了解不同產品的毛利時，這二張的報表都提供了二個階層方式，呈現出市場/產品大類的銷售毛利。主管可以大方向了解不同的產品類別的毛利狀況，但無關銷售數量(不是銷售金額)；如果他想要看的更細，會需要展開至下一階產品名稱，但副作用是A4的報表需要多印個3~5張(視公司的產品數量而異)。為何需要有下一層資訊要濃縮在上一層來顯示的需求呢？因為先Sum再Avg(報表一)的結果，它會將12個不同市場不同產品類別的毛利加總後，再除以12，得到較大方向的狀況。當我們先Avg再Sum(報表二)時，它會先針對N個產品名稱除以銷售的數量得到平均值，接著再加總濃縮後，分別置放在上一階層的12個項目中。換句話說，報表二是綜合各款產品平均毛利至產品類別的結果。

在作法上，首先，我們要新增一個自訂計算，並且使用LOD中Fixed的語法

將新建好的「產品毛利」拖到Sum(Profit)進行取代，並且按右鍵把彙總方式由Sum改成Avg

 將 Sales銷售額拖到 Columns中「產品毛利」的右邊，一樣，按右鍵把彙總方式由Sum改成Avg，接著再做雙軸

將這二個 Measure 做同步軸

最後，經過美工，就是剛才的報表二了

至於在 Power BI要怎麼做呢？答案不外乎就是 ALLEXCEPT 這個 DAX語法，以新獲取客戶為例：

 呈現的結果如下：

同理，如果有預到 Tableau 的 Exclude/Include LOD語法的使用情境，則要用DAX中的 Allselected/Summarize 來對應；

相信大家都聽說過「增加一個新客戶的成本是維持舊客戶的五倍」，我再加碼一個「新舊客戶分析」來驗證這個理論是否能成立？以 Super market資料集為例，一般來說，老闆為了分析公司是否賺錢？會先看Sales 銷售金額與 Order Date 訂單日期的報表

大方向看起來是賺錢的，但我們卻分不出來是舊客戶回購比較多？還是透過廣告或是口碑行銷帶進了新的客戶？另一種方法就是，把銷售金額換成不重複的客戶計數

再搭配與不重複計數的 Order ID訂單編號做雙軸，但是下圖並無法呈現出新舊客戶的貢獻度，那我們該怎麼做呢？

所以我們可以先對資料做一下分析，就會發現每個客戶大多有多筆的交易記錄，如果我們要區分新舊客戶，就是要了解單一個客戶他的消費行為，透過 Fixed 函數就能把 Customer Name 固定下來。另外，若你想要知道買 A產品的客戶，還有買什麼產品，也是使用 Fixed 函數。所以，當你要挖掘更多的客戶行為，一定要善用這個函數。

接著，我們可以命名一個「客戶首購日期」，來進行分析

讓我們來驗證一下這個函數，我打算把原始的訂單日期與首購日期擺在一起做比較，因此會需要一個日期的客製化格式手法… 

進一步選擇 

就能放在一起比較 

另外，你也可以抽出年份，再移到最左邊進行比較，進而了解每個客戶的首購年份 

再回到報表的製作，接下來，我們先將首購日期拖進 Color中，來了解逐年新增客戶的變化 

系統判斷成四條折線圖，但應該要改成柱狀圖，一起比較才有感覺。果然從2015 開店以來，超市有成功地養出一群中流砥柱的舊客戶 

若你不喜歡四年疊在一起看，你也可以按住 Ctrl 把首購日期複製到 Rows中，了解每年內新增客戶的數量 

第四群在逐年的數字上下的變化不大，可以加上表計算，切換為百分比的分析面向 

再透進階地表計算(馬上要介紹了，若看不懂可以先往下看，待會看懂了再回來)，除了預設由橫向做加總(由左至右)，四年合計為100%的分析。也可以改成垂直方向的加總，讓每一年的新舊客戶加總為100%，了解新增客戶更細節的變化 

就能得到以下的結果 

或者是不看 Percent of total 改看 YOY，也只需要把表計算，改成 Year over year growth即可 

在本例中是將不同年的新增客戶視為一個群組，所以 LOD的應用很多，端看於語法的撰寫，來配合不同的應用情境。

五、表計算：
在下圖中有支援11種方式，總共可以歸納為二大類，一個是切分重組你原先的資料 Partitioning fields define the scope，一個是定義資料的移動與方向 Addressing fields define the direction。常見的應用情境，有包含：多少比率的變化(例如某一檔股票在一段時間上漲/下跌的比率)、基準線(例如把公司代理十年的產品與最近三年內代理的新產品們做比較)、(二次)百分率比較(總經理常常在知道絕對值之後，還會想進一步知道相對值。當他知道A產品已經賺了3億，還會想知道佔全公司的營收的百分率，以利他進行資源的調度與營運策略的優化。例如透過二次百分率比較來了解全公司在五年內使用門市通路與電子商務各佔多少百分率，它原來是一張愈來愈高的階梯顯示數字的雙色重疊柱狀圖，雖然可以看出電子商務在這五年一直都高出一些，但是百分率也是很重要的資訊；經過二次百分率的比較，等於是一個二步驟的分析圖表，第一步會是橫向總和為100%的五條不同長度的柱狀圖，第二步則是垂直總和為100%五條等長的柱狀圖)、陡坡圖(Slope chart /Bump chart之前有介紹過，能呈現出一陣時間Top N排名的變化)、迄今累計(常見的Running total)、加權平均(常見的客製化數值分析)、計算分群(例如你正在探討運輸成本時，你可以創建一個表計算，動態地抓出低於平均為一群，高於平均為另一群，而且加上顏色來區分。當成本改變時，你只需要按一下 Refresh，或是下次打開報表時，資料就會有變化)、異常事件儀表板(一種便於我們追蹤與分析 Warning 警告事件出現的次數與頻率的分析，你可以從而得知，異常事件的大/小規模、頻率高/低…)、移動平均(在原始的銷售月報表中，但是主管認為用月來追蹤太慢了，所以想加快其節奏，像是股市技術分析常見的5日、10日、15日均線，你都可以輕易地實現)、與平均的差異(當我們關注每月4個季度的銷售變化，常常會以年度平均當做的基準線，但是這個年度平均可以是各年的平均，也可以是累計年度的平均，視你的需求而定，可以輕易地被實現)

假設你是一個財務主管，當總經理要你幫他準備公司近年內不同產品的盈利/虧損狀況分析，好讓他可以規劃一些優化的決策來最佳化資源的調度。想不到第一個版本，竟然被他打槍…

總經理說他閉著眼睛也知道 Tables是公司毛利最差的產品，他需要了解的是每個產品的盈虧細節在逐年間的變化。例如每個產品每年的潛力都可以做到100分，但是因為市場供給、客戶需求、折扣策略、行銷手法…然而在次年又要歸零，上緊發條重新再戰…你該怎麼做？
首先，你可以 Double click 離散型的 Category & Sub-Category 與 Order Date，以及數值型的產品數量，來繪製一個水平的 Bar chart

接著寫一個函式來判斷盈虧狀況 

對著連續型的數值，按右鍵選擇 Percent of total的表計算，你可以發現X軸的座標會由數值變成百分比 

但由於總經理是要看逐年的盈虧，所以你需要更改表計算的範圍與方向，選擇 Cell，畫面中每個產品在每一年都會變成等長的100% 藍色直條 

然後，再把剛才寫好的函式拖進 Color中 

但自動配出的顏色與事實可能有些出入，所以我們對調一下顏色，讓 Profit 用藍色，Loss 用橙色，以及把做過表計算的數值，按住 Ctrl 拖進 Label中 

如果你覺得似懂非懂，我可以換成明細的數值，並且分解動作給你看。首先，為何畫面中的每個數值都這麼小？因為表計算的預設值是 Table，指的是畫面上所有數值加起來是100%，你可以驗算一下是不是 17個類別的產品，在四年內的 Loss+Profit 全部加起來會等於100？ 

此外，雖然除了Table 還有 Table across /Table down二個選項，但 Table across 只能橫向做到 Loss 在四年加起來 100%；Table down 縱向每一年的所有產品的 Loss + Profit 一直加到最底下總合為 100%，但是總經理要的是每年每個產品 Loss + Profit 為 100%，所以我們來試試 Cell 吧！

當我們選擇 Cell 時，每個最小單位都是100% 

那改成 Pane 可以嗎？以 Bookcases 為例，你將會得到四年八個 Cell 的總合是100%，但總經理是要每年二個 Cell 合計為100% 

所以，你如果選 Pane down就 Bing go了 

當你 Run 過一次，知道表計算有哪些變化後，下次你就可以依據你的情境選擇適合的選項，一擊就中。甚至還可以使用更進階的指定欄位，或是使用 Across then Down，來因應更複雜的客戶需求。

六、其他：
若你想精通 DAX 一定不能錯過的經典之作：The Definitive Guide to DAX, The: Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI 第一版是紫色的，第二版是墨綠色的
**除了DAX，PowerBI 另外還有一個專門用來處理ETL的 M語法，將來有機會再介紹…

七、結論：
針對企業常見的同期比較、Top N，我們分別在項目2、3介紹了時間智慧與上下文篩選的功能，就像是孫悟空拔下了一根頭髮，吹出了分身，來做同質性的比較。或是拔下了N根頭髮，對著空中與地面吹出小孫、小小孫、小小小孫的 Top N排序在不同分母(空中/地面/全部)中做比較。
我們也介紹了 LOD定海神針，如何不必看完所有明細慢慢加總，就能將它下一階的 Insight洞見濃縮至上一階大類中？例如，可以鎖定客戶，了解客戶回購的狀況(新增一個客戶的成長是既有客戶的五倍，所以客戶的回購策略算是很重要的一環)；當老闆了解產品大類的銷售與毛利後，可以再進一步鎖定產品，以了解熱賣商品在產品大類中的表現。好比，一個賽馬大亨，除了想知道哪些馬廄有出好馬之外，可能還會想進一步知道，每個馬廄能出幾匹好馬？
另外，我們也介紹了表計算，可以用來解決像是博物館木乃伊特展的參訪流量與索取文宣的數量，以利館方可以分析週一到週日，在上午/下午/晚上不同的時段，在這樣21格的表格中，選擇左至右的一週分析、上至下時段分析、先左至右再上至下分析、先上至下再左至右的分析，以利資源的調配與調度。就像如意金箍棒，想怎麼用就怎麼用一樣。
最後，不管是 Power BI的 Quick measure 還是 Tableau 的 Table calculation，雖然它們都能依據我們所指定的商務邏輯，快速地替我們產生底層的語法。但也因為很難預料使用者會怎麼使用我們所設計好的報表，若能事先理解，系統是在處理不同顆粒度的資料，與篩選器、上下文篩選、表計算…等細節的優先順序。才是上上之策…

李秉錡 Christian Lee
Once worked at Microsoft Taiwan