[分享] 菜鸟PowerPivot周记(三)销售每平米KPI计算(SUMX)—— 内藏挑战题目!

  [复制链接]
查看133490 | 回复132 | 2021-2-19 02:05:21 | 显示全部楼层 |阅读模式
呼,犯懒了快一个月没有更新学习记录了,想到八月都快到了,赶紧来写一篇吧!(写完都8月了。。。)
KPI的,我用的例子就是店铺销售每平米( 大家可能要问了,这也能算是难题吗?Sales/store size,小学二年级题目啊!Pivot做都可以!)
Sales的表格如下,记录了每一笔交易的日期、店铺、货品和金额;还有一个/ 1702168rmbsktqk2ta5tr6.png 170217fkj54c7gknzuk56f.png
3月开始营业的,那计算本年的面积的话就要加一个系数, 怎么样,还觉得简单吗?
VBA一定做得出来,但对于一个零售公司,全年上百万条的交易记录,恐怕庞大的计算量会令Pivot,肯定有 好,言归正传,看PP是如何简单解决这个问题的!
PP可以像SalesStoreSalesDate 然后,养成一个好习惯,就像上学考试的时候,先把能写Measure的先写上,能得一分是一分!Sale:=SUM(Sales[Sales]) 求所有销售的和Store Size 0:=SUM(Store[size]) 求所有店铺面积
Sales per M2当然不是Store C2010年还有开业,面积也被算进去了,这样670,显然不对喽。
比如我们选取一个时间段,比如C店铺为例,122012年结业,所以在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呢?因为有Store4个值,MAXPivot返回的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,这只会发生在店铺完全没在这段时间营业的情况下,比如这样: 170219b6zdls064c70dyl3.png 当然等效时间就是0啦。
WA ratio:=[Duration]/[period length]
C举例,就可以看到是这样啦: 170220o264tmond12rgzdw.png 把季度放进去,可以看得更清楚一些: 170221tn7w6mreejkun5az.png




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

使用道具 举报

xueshan | 2021-2-19 02:13:21 | 显示全部楼层
于是你把Pivot中: 171331z54roc878uzh8i84.png 坑爹呢这是?!为什么Total???PP脑残了???
就在这两个Measure上边:Close day:=MIN([Close date])Open day放进Open Day都是对的,但OpenDay了,因为我们用了MAXTOTAL的结果又会不同了。所以乱子就出来了。 17133177ue0taougf0sqlq.png
PP是不会让我们失望的,面对这种死局早有对策!X系列X系列就是专门针对这种情况,管它平均还是什么的,强行把所有值加起来!它们有MAXXRANKX 举个例子:Ave Price:=SUMX(VALUES(sales[Product]),[Price]) 171334zo0qq9i5gh1g9wki.png 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,大家可以验算看一下对不对哦! 171335j52jp0d4h06ey6hj.png Sales per m2:=[sale]/[Store size 4] 171329ph2omk1errd2zzvk.png
KPI,但如果要对一个规模较大的公司,每年有几百万比交易,成百上千的店铺遍布各地,而且不断有新店铺开业旧店铺结业的话, //周),麻烦程度可想而知。SAP等大型1个小时可以在IT编程那可是要给老板写计划书的事情。而且一旦完成在很长时间内都不能改变,非常不灵活。
PP也不是万能的,只不过是在这种情况下比SAP更加适合而已,所以叫SAP一样掌控全球数据)所以SAP,联系起来,进行分析,会最大的体现它的价值。
回复

使用道具 举报

znli1979 | 2021-2-19 02:19:22 | 显示全部楼层

再占一个,怕不够用!!!
回复

使用道具 举报

vghao | 2021-2-19 02:24:22 | 显示全部楼层
等了三周{:soso_e163:} 终于来了{:soso_e121:}
回复

使用道具 举报

明记 | 2021-2-19 02:25:22 | 显示全部楼层
附件下不来,麻烦直接传到EH帖子上
回复

使用道具 举报

srici_yb | 2021-2-19 02:33:22 | 显示全部楼层

放上去拉!
回复

使用道具 举报

乔峰 | 2021-2-19 02:36:23 | 显示全部楼层

哈哈,好感动啊!下次尽量不偷懒了。。。
回复

使用道具 举报

Seraph | 2021-2-19 02:40:23 | 显示全部楼层

谢谢您答复
回复

使用道具 举报

卡车司机 | 2021-2-19 02:47:23 | 显示全部楼层
谢谢楼主的分享 详细精彩
回复

使用道具 举报

小东邪 | 2021-2-19 02:53:23 | 显示全部楼层
太强悍了,楼主是通过什么途径学习的,能介绍一下相关的网站、书籍或视频吗?这块内容网上资料很少,我好不容易买到一本书《微软EXCEL2013:用POWER PIVOT建立数据模型》,但是外国人写的,语境不适应,坑爹的出版社还不提供附件,只能单看书,还是太抽象,学起来很吃力啊。
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则