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

显示全部楼层 · 2021-2-19 02:05:21

呼，犯懒了快一个月没有更新学习记录了，想到八月都快到了，赶紧来写一篇吧！（写完都8月了。。。）
KPI的，我用的例子就是店铺销售每平米（大家可能要问了，这也能算是难题吗？Sales/store size，小学二年级题目啊！Pivot做都可以！）
Sales的表格如下，记录了每一笔交易的日期、店铺、货品和金额；还有一个/季

3月开始营业的，那计算本年的面积的话就要加一个系数，怎么样，还觉得简单吗？
VBA一定做得出来，但对于一个零售公司，全年上百万条的交易记录，恐怕庞大的计算量会令Pivot，肯定有好，言归正传，看PP是如何简单解决这个问题的！
PP可以像Sales的Store的Sales的Date的然后，养成一个好习惯，就像上学考试的时候，先把能写Measure的先写上，能得一分是一分！Sale:=SUM(Sales[Sales]) 求所有销售的和Store Size 0:=SUM(Store[size]) 求所有店铺面积
Sales per M2当然不是Store C和2010年还有开业，面积也被算进去了，这样670，显然不对喽。
比如我们选取一个时间段，比如C店铺为例，1日2月2012年结业，所以在C店铺从1日，开到了31日，共150×7/12=87.5. 我们定义我们选择的这个时间段，第一天是Last day；我们选的这段时间2011年的长度Period Length = Last day – First day +1
Open day，结业时间是店铺在我们选择的这段时间开业的第一天是End Day = MIN(Last day, Close day)；Duration = End Day – Start Day +1
WA ratio = Duration / Period Length 就是这段时间店铺最后，Store Size = Store Size 0 * WA ratio
接下来就是简单的写公式了：Last day:=EOMONTH(MAX([Date]),0)（Date表里边了，所以用个函数得到最后一天；如果MAX([date])就可以了）Period Length:=VALUE([Last day]-[First day]+1)别忘加VALUE呢？因为-时间，格式不能变成数字，有待改进啊。。。。
Open day:=MIN([Open Date])MIN呢？因为有Store，4个值，MAX，Pivot返回的Total先不要看哦，目前 End Day:=if([Last day]>=[close day],[close day],[lastday])可惜MAX([Firs day], [Openday])这种格式，里边只能有一个量，还必须是LARGE或者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]
C举例，就可以看到是这样啦：

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

华为网盘附件：
【华为网盘】 3 Advanced Average.rar

3 Advanced Average.rar (117.14 KB, 下载次数: 0)

xueshan · 2021-2-19 02:13:21

于是你把Pivot中：

坑爹呢这是？！为什么Total？？？PP脑残了？？？
”就在这两个Measure上边：Close day:=MIN([Close date])Open day放进Open Day都是对的，但OpenDay了，因为我们用了MAX，TOTAL的结果又会不同了。所以乱子就出来了。

PP是不会让我们失望的，面对这种死局早有对策！X系列X系列就是专门针对这种情况，管它平均还是什么的，强行把所有值加起来！它们有MAXX，RANKX，举个例子：Ave Price:=SUMX(VALUES(sales[Product]),[Price])

SUMX把所有的价钱直接加了起来！
（VALUES返回唯一值，然后Measure，比如[price]加起来）
作用方法可能是这样的（纯猜测）：=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，但如果要对一个规模较大的公司，每年有几百万比交易，成百上千的店铺遍布各地，而且不断有新店铺开业旧店铺结业的话， /月/周），麻烦程度可想而知。SAP等大型1个小时可以在IT编程那可是要给老板写计划书的事情。而且一旦完成在很长时间内都不能改变，非常不灵活。
PP也不是万能的，只不过是在这种情况下比SAP更加适合而已，所以叫SAP一样掌控全球数据）所以SAP，联系起来，进行分析，会最大的体现它的价值。