菜鸟PowerPivot周记（三）销售每平米KPI计算（SUMX）—— 内藏挑战题目！

tc0098

呼，犯懒了快一个月没有更新学习记录了，想到八月都快到了，赶紧来写一篇吧！（写完都8月了。。。）

这次的内容是关于一类经常用的KPI的，我用的例子就是店铺销售每平米（Sales per m2）

。

大家可能要问了，这也能算是难题吗？Sales/store size，小学二年级题目啊！

嘿嘿，大家觉得不难的话，请接招来挑战一下吧！（用公式或者Pivot做都可以！）

题目如下（附件可以下载）：

一个公司（简化的）有一个Sales的表格如下，记录了每一笔交易的日期、店铺、货品和金额；还有一个Store的表格记录了每个店铺的面积、开业时间和结业时间。求某个时间（年/季/月）某个店铺（或者所有店铺）的每平米销售金额。

题目解析：此题的难点在于如何确定某个时间段其中一个店铺的面积，比如一个店铺是在年中3月开始营业的，那计算本年的面积的话就要加一个系数，10/12，来换算这个店铺的等效面积；但如果是计算本年第二季度的话，这个系数就不需要了。

怎么样，还觉得简单吗？

不过想必各位大神用公式，数组，VBA一定做得出来，但对于一个零售公司，全年上百万条的交易记录，恐怕庞大的计算量会令Excel文件直接崩溃吧？而普通的Pivot，肯定有SQL高手藏龙卧虎，不过本菜鸟没有什么研究，完全不会啊。。。

好，言归正传，看PP是如何简单解决这个问题的！

首先，PP可以像Access一样表和表之间的关系，Sales的Store当然和Store的Store连接啦，Sales的Date和Date的Date连接啦。（具体步骤就不写啦）。

然后，养成一个好习惯，就像上学考试的时候，先把能写Measure的先写上，能得一分是一分！

Sale:=SUM(Sales[Sales]) 求所有销售的和

Store Size 0:=SUM(Store[size]) 求所有店铺面积

然后我们想知道的Sales per M2当然不是Sale/Store Size 0啦：

把Store Size 0搬进Pivot:

因为我们没有做任何的定义，所以，比如，即便Store C和D在2010年还有开业，面积也被算进去了，这样2010年的总面积就是670，显然不对喽。

来让我们这么想：

比如我们选取一个时间段，比如2011年，想知道这个时间段的店铺等效面积，以C店铺为例，6月1日2011年开业，2月1日2012年结业，所以在2011年，C店铺从6月1日，开到了12月31日，共7个月。等效面积就是150×7/12=87.5.

逻辑如下图：

我们定义我们选择的这个时间段，第一天是First day，最后一天是Last day；

我们选的这段时间2011年的长度Period Length = Last day – First day +1

店铺的开业时间是Open day，结业时间是Close day；

店铺在我们选择的这段时间开业的第一天是Start day = MAX(First day, Open day)，最后一天End Day = MIN(Last day, Close day)；

所以店铺这段时间开业的时间Duration = End Day – Start Day +1

最后WA ratio = Duration / Period Length 就是这段时间店铺C的等效面积的系数了

最后，Store Size = Store Size 0 * WA ratio

逻辑简单明了吧？

接下来就是简单的写公式了：

First day:=MIN([Date])

Last day:=EOMONTH(MAX([Date]),0)

（EMONTH大家都熟悉吧，就是求某段时间月底的最后一天，因为我的懒得把所有日期都放在Date表里边了，所以用个函数得到最后一天；如果Date表完整的话，MAX([date])就可以了）

Period Length:=VALUE([Last day]-[First day]+1)

别忘加1哦！为什么要加个VALUE呢？因为PP设定有问题，时间-时间，格式不能变成数字，有待改进啊。。。。

Open day:=MIN([Open Date])

Close day:=MIN([Close date])

为什么要写MIN呢？因为有4个Store，[Opendate]是4个值，measure是不能等于一个列的，所以必须加一个函数返回一个其中的值。MAX，SUM的效果也是一样的效果。

Pivot返回的Open day值如下，完全正确！（Total先不要看哦，目前Total是没有意义的！Total是SUM下的效果，其实是什么都无所谓）

Start day:=if([first day]>=[Open day],[Firstday],[open day])

End Day:=if([Last day]>=[close day],[close day],[lastday])

可惜PP里边不能写MAX([Firs day], [Openday])这种格式，里边只能有一个量，还必须是Column，目前还不知道有什么特殊考虑，并且没有LARGE或者SMALL，所以勉强用IF代替一下吧。

Duration:=IF(VALUE([End Day]-[Startday]+1)<=0,0,VALUE([End Day]-[Start day]+1))

这里有个判断，为了避免有时候会出现的错误：End Day<Start day，这只会发生在店铺完全没在这段时间营业的情况下，比如这样：

当然等效时间就是0啦。

WA ratio:=[Duration]/[period length]

Store Size 3:=[Store Size 0]*[WA ratio]

拿店铺C举例，就可以看到是这样啦：

把季度放进去，可以看得更清楚一些：

这样就结束了吗？请看这个问题，老板说不想单个单个店铺看了，我们想看整个公司的销售每平米！

华为网盘附件：
【华为网盘】 3 Advanced Average.rar
3 Advanced Average.rar (117.14 KB, 下载次数: 295)

2013-8-2 11:08 上传

点击文件名下载附件

tc0098

于是你把Store Size 3按这样的方式放进Pivot中：

坑爹呢这是？！为什么A+B+C+D不等于Total？？？PP脑残了？？？

哈哈，这个就有意思了！试想一下，如果你在计算平均值的时候，每个产品分别的平均值加起来是否等于全部产品的平均值呢？显然不是吧。这里是一样的道理，刚刚我们做的那些步骤，仅仅是对某一个店铺有效的，合起来的话，必然出乱子。

“错误”就在这两个Measure上边：

Open day:=MIN([Open Date])

Close day:=MIN([Close date])

比如把Open day放进Pivot里去，每个店铺的Open Day都是对的，但Total的话，就全是最小那个OpenDay了，因为我们用了MIN。好记性的读者一定记得这里用MAX，SUN都是可以的，那TOTAL的结果又会不同了。所以乱子就出来了。

当然，强大的PP是不会让我们失望的，面对这种死局早有对策！

下面隆重推出X系列DAX！X系列就是专门针对这种情况，管它平均还是什么的，强行把所有值加起来！它们有SUMX，MAXX，MINX，RANKX，COUNTX。。。

举个例子：

Price:=AVERAGE([Sales])  

Ave Price:=SUMX(VALUES(sales[Product]),[Price])

SUMX把所有的价钱直接加了起来！

（X系列的原理我还不是那么清楚，只知道这一个用法，用VALUES返回唯一值，然后SUMX就会把这个几个唯一值对应的Measure，比如[price]加起来）

平时这个函数看起来没什么用，但此时此刻，就是它大放光彩之时！

Store size 4:=SUMX(values(Store[Store]),[Store Size 3])

作用方法可能是这样的（纯猜测）：

= SUMX({A,B,C,D},[Store Size 3])

=SUM([Store Size 3] for A, [Store Size 3] for B, [StoreSize 3] for C, [Store Size 3] for D)

然后我们就得到了这样的Pivot，大家可以验算看一下对不对哦！

最后水到渠成：

Sales per m2:=[sale]/[Store size 4]

后记：

这只是个小小的例子，这个小例子用公式甚至用计算器都可以算的出来销售每平米这个KPI，但如果要对一个规模较大的公司，每年有几百万比交易，成百上千的店铺遍布各地，而且不断有新店铺开业旧店铺结业的话，PP的优势就毫无疑问的体现了出来。

Excel肯定无法自动化这样的计算，之前必定要经过一些整理，减小录入数据的大小，再加上很多手工的计算，如果要涉及更多时间段的比较（季度/月/周），麻烦程度可想而知。

SAP等大型ERP系统自然可以解决，但之前要投入巨大的人力财力，用1个小时可以在PP上解决的事情，找IT编程那可是要给老板写计划书的事情。而且一旦完成在很长时间内都不能改变，非常不灵活。

当然，PP也不是万能的，只不过是在这种情况下比Excel和SAP更加适合而已，所以叫Self-ServedBI System：目的就是让个人具备分析较大数据的能力。（但显然无法像SAP一样掌控全球数据）所以PP和一个良好建立的数据库，比如SAP，联系起来，进行分析，会最大的体现它的价值。

tc0098

tc0098 发表于 2013-8-1 17:09
赶紧占楼！！！！！！

再占一个，怕不够用！！！

在和弦处等你

等了三周{:soso_e163:} 终于来了{:soso_e121:}

在和弦处等你

附件下不来，麻烦直接传到EH帖子上

tc0098

在和弦处等你 发表于 2013-8-2 10:15
附件下不来，麻烦直接传到EH帖子上

放上去拉！

tc0098

在和弦处等你 发表于 2013-8-2 09:22
等了三周 终于来了

哈哈，好感动啊！下次尽量不偷懒了。。。

在和弦处等你

tc0098 发表于 2013-8-2 11:11
哈哈，好感动啊！下次尽量不偷懒了。。。

谢谢您答复

solini

谢谢楼主的分享 详细精彩

540223905

太强悍了，楼主是通过什么途径学习的，能介绍一下相关的网站、书籍或视频吗？这块内容网上资料很少，我好不容易买到一本书《微软EXCEL2013：用POWER PIVOT建立数据模型》，但是外国人写的，语境不适应，坑爹的出版社还不提供附件，只能单看书，还是太抽象，学起来很吃力啊。