帕累托分析模板

谨以此文纪念意大利经济学家帕累托。

很多人都知道80/20帕累托法则（20%的人掌握着80%的财富），而ABC分类法可以说是该法则的衍生，目的是把握关键，分清主次。

比如一共有100件商品，

10件商品占销售总额的70%；

20件商品占销售总额的20%；

还有70件商品仅占销售总额的10%。

于是你可以按照70%，20%，10%的销售额比重把产品分为ABC三类，然后把重点的管理资源放在A，把较少的资源分配给C或者砍掉部分C商品，以达到资源管理的最优状态。（下图是我使用案例数据做的一个样例）

1879年，自伟大的帕累托创造了该法则后，该方法不断地应用在管理的各个方面，从库存管理、质量管理、销售管理甚至到社会分析，成为企业提高效益的普遍管理方法。只不过在没有计算器的年代，人民群众应该是用纸笔按照下面的步骤来计算的。

现在有了Excel，完成此类计算并不是什么难事，而且Excel2016还集成了帕累托分析图。

然而，随着经济的发展，数据量增加，维度不断丰富。比如公司的连锁店开遍全国各地，喝杯咖啡有几十个品种供你选择，到超市里买瓶啤酒大多数都叫不出名字...在繁多的维度和数据体量下，制作一张静态图表已经满足不了分析需求。

比如我们想快速的做出2017年1/2/3月，北京/杭州/上海分店，酒类/咖啡/饮料不同类别商品的帕累托图，并且知道ABC类商品都有哪些？C类商品的占比每个月是上升了还是下降？

仅仅是刚刚提到的这几个时间、地点、商品种类维度，就可以组合出27种不同的可能，难道我们要像上面填表的方法重复制作27遍？如果维度更多，定期制表，这个工作量简直是难以想象！

答案

我还是直接来揭晓答案吧，解决这种问题，需要的是动态ABC分析，而不是基于某一张静态表来计算，比如达到下图的效果，点击不同选项就可以快速得到分析结果。当然对于PowerBI，当数据源有更新，所有图表只要一键刷新即可。

当你的领导第一次看见这样的分析，很有可能是心潮澎湃，内牛满面。

实现这个动态分析的方法并不难

现在就来详解怎样去制作动态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分类表。

最后，制作一张帕累托分析图，它其实就是一张折线与柱形图。只不过我用了个小技巧，把销售额的柱形颜色设置为白色，所以在白色背景下隐藏了起来，再利用图表右上角的排序功能按照销售额由大到小降序排列。

至于求商品个数和占比，以及添加各种分类的切片器和百分比图来实现多维度分析的效果，这不是什么新知识而且比较简单，我不在这里做特别说明了。

你可以在文尾原文链接中免费下载该模板和案例数据，看到图表和公式的详细做法。

关于此模板

首先要感谢来自餐饮行业的数据分析主管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%的患者是呼吸道类疾病并且来自同一个地区，从而推测该地区可能存在空气污染源...

我相信在广大的读者中，一定有人将立刻掌握这个模板并应用到实际工作中，创造出不可估量的决策价值！