最實用的帕累托分析模板

謹以此文紀念義大利經濟學家帕累托。

• 帕累托與ABC分類法

很多人都知道80/20帕累托法則（20%的人掌握著80%的財富），而ABC分類法可以說是該法則的衍生，目的是把握關鍵，分清主次。

比如一共有100件商品，

20件商品占銷售總額的20%；

於是你可以按照70%，20%，10%的銷售額比重把產品分為ABC三類，然後把重點的管理資源放在A，把較少的資源分配給C或者砍掉部分C商品，以達到資源管理的最優狀態。（下圖是我使用案例數據做的一個樣例）

1879年，自偉大的帕累托創造了該法則後，該方法不斷地應用在管理的各個方面，從庫存管理、質量管理、銷售管理甚至到社會分析，成為企業提高效益的普遍管理方法。只不過在沒有計算器的年代，人民群眾應該是用紙筆按照下面的步驟來計算的。

現在有了Excel，完成此類計算並不是什麼難事，而且Excel2016還集成了帕累托分析圖。

然而，隨著經濟的發展，數據量增加，維度不斷豐富。比如公司的連鎖店開遍全國各地，喝杯咖啡有幾十個品種供你選擇，到超市裡買瓶啤酒大多數都叫不出名字...在繁多的維度和數據體量下，製作一張靜態圖表已經滿足不了分析需求。

比如我們想快速的做出2017年1/2/3月，北京/杭州/上海分店，酒類/咖啡/飲料不同類別商品的帕累托圖，並且知道ABC類商品都有哪些？C類商品的佔比每個月是上升了還是下降？

僅僅是剛剛提到的這幾個時間、地點、商品種類維度，就可以組合出27種不同的可能，難道我們要像上面填表的方法重複製作27遍？如果維度更多，定期製表，這個工作量簡直是難以想像！

• 答案

我還是直接來揭曉答案吧，解決這種問題，需要的是動態ABC分析，而不是基於某一張靜態表來計算，比如達到下圖的效果，點擊不同選項就可以快速得到分析結果。當然對於PowerBI，當數據源有更新，所有圖表只要一鍵刷新即可。（下圖請手機橫屏觀看，該儀錶板也可以通過鏈接直接進入體驗）

當你的領導第一次看見這樣的分析，很有可能是心潮澎湃，內牛滿面。

實現這個動態分析的方法並不難（如果它很難掌握，PowerBI大師是不會收錄的，因為我的目標是讓更多的人站在Excel的肩膀上）

現在就來詳解怎樣去製作動態ABC分析儀錶板。我們從數據源開始上手，一共有5列，年份月份從1月到7月，商品名稱，類別有酒類、咖啡和飲料，城市有3座北京、上海和杭州。

首先基於該表可以簡單地寫一個度量值，求銷售金額合計。

[銷售額]=Sum("銷售明細表"[銷售金額])

有了度量值你可以嘗試著去構造一張ABC模樣的表。把[商品名稱]欄位放到透視表的行，值放入[銷售額]，再按照由大到小降序排列。

接下來是最核心的部分，求累計銷售額，它的度量值公式是：

這個公式是怎樣工作的呢？比如以張裕解百納這個單元格為例，輸出的結果為15,999,398。我把計算過程分解成以下5個步驟：

1.先利用VAR定義當前商品的[銷售額]，currentsales可以得到1,420,202。（對VAR的用法如有疑惑可以參見我之前寫過的文章PowerBI公式-VAR）。

2.下面是最核心的部分，如何篩選出大於等於1,420,202的商品表呢？利用All函數，All("銷售明細表"[商品名稱])可以得到一張不重複的商品名稱清單表。（下圖僅是為了模擬公式的運算過程，實際並無此操作）

3.Addcolumns的作用是在該All表的後面加入一列「銷售額列」，求得每種商品的銷售額。

4.Addcolumns的妙處是剛剛添加完的這個「銷售額列」可以再次被利用起來。使用Filter("Addcolumns表", [銷售額列]>=currentsales)，就可以把[銷售額列]大於等於1,420,202的所有商品都篩選出來。

5.最後Calculate([銷售額],Filter())把這些商品的銷售額總計求了出來，得到了15,999,398。有了累計銷售額，計算累計金額的百分比並不難，即累計銷售額除以總計銷售額。

求總計銷售額可使用Calculate([銷售額],All("銷售明細表"[商品名稱]))。

以上是累計金額百分比的分步計算邏輯，為了方便，我在案例數據演示中把這些分步寫到了一個度量值公式裡面，現在你可以整體的去看這個公式的邏輯。

如果你理解了這條公式，恭喜你已經掌握了該模板最困難的部分。接下來想要做ABC分類其實就是基於不同的累計金額百分比70%,20%,10%做判斷區分。比如寫一個度量值[A類銷售額]

仍然用張裕葡萄酒的例子，這裡的values函數將返回張裕葡萄酒的商品名稱，它的累計金額百分比是33.28%，滿足<=70%的條件，所以該項目將求得它的銷售額。如果不滿足<=70%的條件將返回空值。

同樣的方法，再寫兩個度量值B和C。

至此，把建立好的度量值拖動到透視表中即可製成ABC分類表。

最後，製作一張帕累托分析圖，它其實就是一張折線與柱形圖。只不過我用了個小技巧，把銷售額的柱形顏色設置為白色，所以在白色背景下隱藏了起來，再利用圖表右上角的排序功能按照銷售額由大到小降序排列。

至於求商品個數和佔比，以及添加各種分類的切片器和百分比圖來實現多維度分析的效果，這不是什麼新知識而且比較簡單，我不在這裡做特別說明了。

你可以在公眾號的知識店鋪中免費下載該模板和案例數據，看到圖表和公式的詳細做法。（對於購買過PowerPivot課程的學員，我已經加入了全程的演示和公式詳解的視頻教程）

• 關於此模板

首先要感謝來自餐飲行業的數據分析主管Jerry貢獻了案例，讓我的腦洞小小的開了一下，想出這樣一個模板。你可能想知道這個模板的難度等級是多少？

在SQLBI網站上有記載動態ABC的實現方法，難度評級為4級（還沒有5級的），也就是頂級，原因之一是因為它的公式基本是按照ABC製表邏輯構建，並且利用輔助表把70,20,10分類，公式很長。說實話，在我讀完後感覺燒掉了好多腦細胞。相比之下，我寫的公式僅有五六行，而且只用了數據源一張表，思路清晰且演算法更優。

在上一篇文章PowerBI大師知識變現能力分析使用到的購物籃分析也是以極簡的方法實現了4級難度的模板。

這就是DAX語言的魅力，不在於你掌握多麼技術多麼深奧的公式，重要的是如何把簡單的公式組合到一起，幻化出無窮的力量。這需要你的想像力。邏輯可以把你從A帶到B，想像力可以帶你去任何地方！

因為很多DAX學習者都在追隨SQLBI的DAX Pattern來學習，為了求證，我寫了一封郵件給SQLBI的創始人Marco Russo。

如果你不知道Marco是誰，簡單介紹一下，這位義大利人可以說是全世界DAX領域最具有權威的人（他寫的DAX指南被很多人稱作DAX聖經），著作被翻譯成各國語言傳播。我向他解釋了我對這個模板的想法並得到了認可，他也在創作新的DAX Pattern，將引入像VAR這樣的新公式來實現模板的簡單化和提高可推廣性。敬請期待！

我也把這封郵件公開給廣大的DAX愛好者。

• 為什麼是最實用

既然大膽地使用了「最實用」這個標題，我不得不自圓其說一下為什麼。有三點主要原因：

第一，這個方法並不複雜，任何PowerBI、Excel PowerPivot、DAX的初學者都可以學習掌握，即使不懂你也可以把該模板直接複製使用。所謂「大道至簡」就是最簡單的才是最牛逼的，這個模板可以被很多人拿去使用，但沒有它，對於業務分析人員想要實現這類動態帕累托分析，幾乎是不敢想像的事情。

第二，本篇文章，案例數據和模板都是免費的，就連使用工具PowerBI桌面版也是免費的。但如果沒有它，很多公司想要實現這類分析是通過漫長的IT部門建設項目或者掏出高昂的諮詢費用。現在你可以自助式地使用PowerBI或Excel完成此項任務。

第三， 帕累托分析的應用極為廣泛，從數據輸出的分析結果非常清晰，並且將直接影響資源的分配。

如果你是圖書館管理人，發現80%的人都在閱讀經濟類書籍，可以把有限的預算更多的花在該類圖書上；如果你負責倉庫管理，發現有10%數量的貨品佔據了所有貨品價值的80%，可能由此設定對該部分貨品每周盤點，而其他的貨品可以每個月或者每個季度做一次盤點；如果你來自質量檢查部門，發現80%的殘缺品是因為設備供電不足原因導致，自然是把工作重點和預算放在解決電力方面；如果你在為一家醫院做分析，發現近期的80%的患者是呼吸道類疾病並且來自同一個地區，從而推測該地區可能存在空氣污染源...

我相信在廣大的讀者中，一定有人將立刻掌握這個模板並應用到實際工作中，創造出不可估量的決策價值！

最後，此模板和數據可到我的公眾號的知識店鋪中找到鏈接免費下載使用。

祝好，

微信公眾號PowerBI大師

---------------------------分隔線------------------------------------------

補充說明：

此模板在計算累計銷售額的時候，使用了Addcolumns先建立一張含商品名稱和銷售額的虛擬表，然後篩選銷售額大於等於當前銷售額的商品。

感謝PowerBI愛好者臨淵的反饋，在發文後的第一時間提出不用Addcolumns解決的方式，即

累計銷售額=

Calculate([銷售額],

就可以完成該項運算。我認為這個方法更是降低了該模板的使用難度，對於很多不會使用Addcolumns的人也可以很快上手。另外，這種寫法讓我聯想到了劉凱老師寫的一篇關於Var+Calculate+Filter+All的萬能組合，建議大家在思考方案時，能夠熟練使用這類常用的句型來達到不變應萬變的效果。

寫DAX是條條大路通羅馬，很多時候是無論黑貓白貓抓住耗子就是好貓。但作為模板普及推廣使用，感謝每一位讀者的反饋、質疑和精益求精的問題，以達到極簡極優的方式。

——馬世權