PowerBI實現全動態ABC分析幫助找出業務中的主要因素

來自專欄 簡快Excel之PowerBI建模分析

作者：佐羅

ABC分類法又稱帕累托分析法，主要用於分清管理對象的主次，並分為A，B，C三類。市面上有很多講述用Excel或PowerBI實現ABC分析的方法。而本文將講述異常強大靈活的全動態ABC分析法。

ABC分析法簡介

一般地，基於關注的某類管理對象以及對它的測量構建ABC模型。例如：某零售企業有500個SKU以及這些SKU對應的銷售額，那麼哪些SKU是重要的呢，這就是在業務運營中分清主次的問題。

一些常見的做法是在Excel或PowerBI中，將產品SKU作為維度 並 將對應的銷售額作為基礎度量指標，將這些銷售額指標從大到小排列，並計算截止當前產品SKU的銷售額累計合計佔總銷售額的百分比。

• 百分比在 70%（含）以內，劃分為 A 類。
• 百分比在 70~90%（含）以內，劃分為 B 類。
• 百分比在 90~100%（含）以內，劃分為 C 類。

由此，便可以得到運營管理中的主要因素集合。

值得注意的是，這個方法是通用的，不光可以用來劃分產品和銷售額，還可以劃分客戶及客戶交易額等。這將形成如下的曲線：

可以看出，由於 A 類元素是主要因素，所以它們更快的促進指標的變化，表現在曲線的陡峭爬升上。

當然，ABC分析還可以用於更多的運營管理方面，這就導致一些傳統方法的不便性。

傳統ABC分析法的局限

主要是一點：不夠靈活。體現在：

• A、B、C的佔比需要自定義設置，由用戶通過滑桿拖拉形成，而不是事先規定。
• 要分析的對象元素應該可以動態指定，例如可以是產品，城市，客戶，客戶職業，客戶行業等多種運營管理要素。
• 要分析的對象指標應該可以動態指定，例如可以是交易額，利潤，成本，利潤率，同比增長等多種運營管理指標。
• 要計算的元素空間應該可以動態選擇，例如購買特定產品的客戶，某個地區的門店，以及在產品SKU中排除某幾個特別項等。

如果能同時滿足上述四點，不管在Excel或PowerBI中實現都存在一定的挑戰。尤其是Excel，幾乎很難做到這種動態性。這些管理要素的排列組合相當之多，所以本文正是通過PowerBI的方法來給出一個解決方案。

PowerBI中的表結構

表基礎結構如下所示：

滿足維度建模的業務表，並單獨製作 ABC分析主題輔助表 ，不對以後的業務模型做侵入式的破壞，這裡沒有任何一個計算列，完全依賴度量值完成設計，保持靈活性。

• 基本指標表，包括了常用的度量值，將作為用戶可以動態選擇的指標來源。
• ABC分類，非常簡單，僅僅是分類維度。
• ABC指標，是與ABC分析有關的所有指標度量值。
• ABC元素，是對可能發生的管理要素的 邏輯抽象，是能實現通用性的設計基礎。

ABC元素

這裡將可能被管理的業務元素做了抽象，並用計算表整合在一起，如下：

ABC元素 =VAR Customers1 = SELECTCOLUMNS ( ADDCOLUMNS ( DISTINCT ( 客戶[職業] ), "元素類型", "客戶職業" ), "元素名稱", [職業], "元素類型", [元素類型] )VAR Customers2 = SELECTCOLUMNS ( ADDCOLUMNS ( DISTINCT ( 客戶[行業] ), "元素類型", "客戶行業" ), "元素名稱", [行業], "元素類型", [元素類型] )VAR Citys = SELECTCOLUMNS ( ADDCOLUMNS ( DISTINCT ( 地區[省/自治區] ), "元素類型", "省份" ), "元素名稱", [省/自治區], "元素類型", [元素類型] )VAR Products = SELECTCOLUMNS ( ADDCOLUMNS ( DISTINCT ( 產品[子類別] ), "元素類型", "產品" ), "元素名稱", [子類別], "元素類型", [元素類型] )RETURN UNION ( Customers1, Customers2, Citys, Products )

這裡將可能受到管理的要素如：產品子分類，客戶職業，客戶行業，城市統稱為元素，做的這一層抽象，是這個模型的精妙所在。

ABC分析的度量值

本例的ABC分析中涉及多個度量值，它們大致構成這樣一種依賴關係圖譜：

如果發現，度量值需要相互調用，可以用思維導圖的方式整理出主要結構。

其中，ABC 元素 價值 使用虛擬關係動態構建計算，如下：

ABC 元素 價值 =VAR ItemRelationOfCustomer1 = TREATAS ( VALUES ( ABC元素[元素名稱] ), 客戶[職業] )VAR ItemRelationOfCustomer2 = TREATAS ( VALUES ( ABC元素[元素名稱] ), 客戶[行業] )VAR ItemRelationOfCity = TREATAS ( VALUES ( ABC元素[元素名稱] ), 地區[省/自治區] )VAR ItemRelationOfProduct = TREATAS ( VALUES ( ABC元素[元素名稱] ), 產品[子類別] )RETURN SWITCH ( TRUE (), SELECTEDVALUE ( ABC元素[元素類型] ) = "客戶職業", CALCULATE ( [銷售 指標 自動], ItemRelationOfCustomer1 ), SELECTEDVALUE ( ABC元素[元素類型] ) = "客戶行業", CALCULATE ( [銷售 指標 自動], ItemRelationOfCustomer2 ), SELECTEDVALUE ( ABC元素[元素類型] ) = "省份", CALCULATE ( [銷售 指標 自動], ItemRelationOfCity ), SELECTEDVALUE ( ABC元素[元素類型] ) = "產品", CALCULATE ( [銷售 指標 自動], ItemRelationOfProduct ), BLANK () )

這樣就保證了，在上述度量值圖譜中，只需要和該 ABC 元素 價值打交道，而不必管到底這個度量值在計算什麼，是銷售額，還是利潤，還是其他，完全由用戶在分析時動態決定。這也是本模型的最大特色所在。

這個技巧是通用性的，可以借鑒植入您自己的建模設計中。另外，這裡用到了面向對象編程思想中的開放閉合原則設計模式，在我的課程《用戶自動化運營分析》有更加仔細的描述。

其他度量值，例如 ABC 元素 價值 佔比，如下：

ABC 元素 價值 佔比 =VAR Items = CALCULATETABLE ( DISTINCT ( ABC元素[元素名稱] ), ALLSELECTED ( ABC元素[元素名稱] ) )VAR Total = CALCULATE ( [ABC 元素 價值], Items )VAR CurrentItemValue = [ABC 元素 價值]RETURN DIVIDE ( CurrentItemValue, Total )

這些度量值的計算僅僅依賴於抽象的ABC元素即可。

ABC分析中的核心度量值是累計佔比位，就是考察當前元素到達了多大的累計百分比，如下：

ABC 元素 價值 累計佔比位 =VAR Items = CALCULATETABLE ( DISTINCT ( ABC元素[元素名稱] ), ALLSELECTED ( ABC元素[元素名稱] ) )VAR Total = CALCULATE ( [ABC 元素 價值], Items )VAR CurrentItemValue = [ABC 元素 價值]VAR CumulativeValue = CALCULATE ( [ABC 元素 價值], FILTER ( Items, [ABC 元素 價值] >= CurrentItemValue ) )RETURN DIVIDE ( CumulativeValue, Total )

它的DAX實現也非常簡單，這裡再重複一個技巧，不要試圖寫出連鎖的公式，而是把公式拆解開來：

• Items 計算當前選擇的元素範圍；
• Total 計算當前選擇的元素範圍對應的元素價值合計；
• CurrentItemValue 計算當前元素的價值
• CumulativeValue 計算積累至當前元素的積累價值
• 返回積累價值與價值合計的比例

邏輯上非常簡單可行，這樣就構建了ABC分析的主體框架。

ABC分析的可視化

ABC分析的可視化有行業默認的作圖習慣，那就是用組合圖來表示。但這裡的一個挑戰在於如果真的嘗試去建立這個著名的帕累托圖，要顯示A，B，C用不同的色系（圖例）以標明不同的顏色，如下：

這裡要注意兩點：

• 用ABC類別做篩選，需要為其單獨建立度量值（動態度量值），若正好為當前類別，計算，否則顯示空，以便形成可區分的顏色效果。
• 由於上述的這種做法導致無法正確排序，所以必須將用於排序的度量值加入工具提示，以便正確排序。

ABC分析的動態性

本案例的設計以滿足解決前述四大痛點而存在，因此：

• 第一步，用戶可以拖動滑竿來選擇A，B，C參數的動態相對大小，系統自動計算比例關係；
• 第二步，用戶選擇需要分析的元素類型：產品、客戶行業、客戶職業或城市；
• 第三步，用戶選擇需要分析的元素指標：銷售額、利潤、訂單數；
• 第四步，用戶可以篩選需要計算的元素範圍；
• 第五步，用戶進一步篩選需要計算的元素範圍，與上一步共同構成精細化的選擇；

值得注意的問題

負值問題

由於本案例的靈活性，對於某些指標的計算會出現不符合常規，但卻又真正符合實際的情況，例如：利潤可能出現負值，那ABC分析會如何呈現的，如下：

由於負值的產生會導致出現超過100%的情況，但最終是以100%結束的，但這不影響找到主要因素。如果確實要避免這樣情況，也是可以很簡單的調整來滿足。

最終結果

ABC分析的歸宿之一是顯示ABC三類的各自總價值佔比，如下：

數字化顯示

本案例給出了PowerBI書籤按鈕實現數據化顯示切換的效果，這樣可以在查看繁雜數據與可視化之間靈活切換，如下：

總結

一起來看看ABC全動態分析的效果吧：（動畫）

通過這個案例以及前述的RFM，NPS等模型，應該可以體會PowerBI DAX 模型動態化設計的通用做法套路。在ABC分析中，這裡幫助業務人員從各種管理元素與指標結合中找到主要因素並作出反應。

歡迎加入微信群交流學習

http://weixin.qq.com/r/GXiVjTfE2GqnrXPu9y0L (二維碼自動識別)

若感覺本文有所幫助或需要源文件深入研究

分享轉發截圖私信

線下活動預告

本周 4月15日 上海 下午 13:00 到 17:00

Excel / PowerBI乾貨分享會

~ 場地有限 30人滿 私信報名 ~

http://weixin.qq.com/r/qDqLk2LEKz9UrSbw92_6 (二維碼自動識別)