ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 17449|回复: 23

[分享] 菜鸟PowerPivot周记(四)【完成!】Price & Quantity Effect Analysis(SUMX)

[复制链接]

TA的精华主题

TA的得分主题

发表于 2013-12-3 15:06 | 显示全部楼层 |阅读模式
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖已被收录到知识树中,索引项:Powerpivot
本帖最后由 tc0098 于 2013-12-4 23:02 编辑

前言:
唉,周记变月记,月记变季记,实在是懒的丧心病狂啊。。。为了督促自己,在此立贴为证,本周之内,完成通过PowerPivot解决Price & Quantity Effect Analysis的问题!如无法完成,注销账号!{:soso_e130:}

先做介绍一下定义吧,Sales = Price * Quantity (简写:S=P*Q)
一个货物,上年卖了200,今年卖了275,想知道价钱和数量对销售增长的影响各有多大,就是按下表来计算:
Picture0.png
数量的影响是50,价钱的影响是25。原理就像下边这个图一样啦。
Picture1.png

如果货物只有一个的话,计算当然很简单啦。但如果一个公司有上千种货物,则需要计算每一个货物的P&Q Effect,然后再加起来才是整个公司的P&QEffect

这个自然可以用万能的Excel计算,用Sumifs可以完成,但还是一样的道理ExcelSumifs所占用的资源非常庞大,如果要计算几万行数据的P&QEffect,基本就是不断死机的节奏,而且如果想看和上个月比较,上个季度比较,不同店铺比较等等,写公式估计就要写死了。所以,PowerPivot的优势就在这里体现出来了。

好,接下来开始解题!

首先,建立表和表的关系!
Picture2.png
然后开始写DAX公式!

我们先对公式进行一下小小的变换:

Q Effect:=P0*(Q1-Q0) = S0/Q0*(Q1-Q0)   (因为我们价格的信息是销售除以数量出来的)
P Effect:= (S1-S0)- Q effect   (能简单就简单!)

然后就是如何计算出每个量啦(S1,Q1,S0,Q0)。

S1Q1都是今年的销售量和销售数量,很好用DAX公式表达:
S1:=sum(transaction[SalesValue])
Q1:=sum(transaction[SalesQty])

S0Q0稍稍麻烦一些,需要用到DATEADDDAX公式。

PowerPivot拥有一套非常强大的日期计算逻辑,不同于Excel里边把190011日当作1,然后不断累积的逻辑,PowerPivot直接把日期当作日期来看了,具体是怎么样呢?我也不清楚,反正就是比Excel高级很多就是了。逻辑上的进步当然也带来了公式上的创新,各种关于日期的DAX公式可以方便的满足几乎所有的需求,DATEADD就是其中一个很好用的公式。

DATEADD逻辑很简单,就是把日期提前一段,可以是年、季度、月、日什么的。如果大家还记得第二篇日志关于如何求上年销售增长率(应该没人记得了。。。),要用到AllFilter等高级DAX和复杂逻辑,DATEADD则把一切变得简单粗暴了!
S0: =Calculate([S1],DATEADD(Date[DateKey],-1,Year))
Q0: =Calculate([Q1], DATEADD(Date[DateKey],-1,Year))

效果如图
Picture3.png
公式的意思很明了吧,就是求目前的销售,但把日期推前一年(就是求上一年的销售啦)。

要注意的是,这里引用的日期列一定要是DATE里边的完整日期列,因为Transaction里边的日期可能不是连续的,DATEDD会拒绝使用;然后Year可以换成Quarter,Month, 或者Day

然后来到重点啦,开始计算Q Effect,先写个公式。

Q Effect 1:=S0/Q0*(Q1-Q0)   

如果是这个公式的话,就大错特错啦!因为这个公式是计算所有货物的平均价钱(S0/Q0,然后再乘以所有货品销售数量的差。而计算方法一定要是(之前提到过哦):

一个公司有上千种货物,则需要计算每一个货物的P&QEffect,然后再加起来才是整个公司的P&Q Effect

所以,逻辑要变成,先计算第一种货物的Q effect,然后第二种的Q effect。。。再把所有的Q effect加起来才可以。肿么办呢?肿么办呢?

如果大家记得第三篇日志关于求店铺平均面积的放法(应该没有人记得了。。。),那个这个问题就可以迎刃而解!SUMX再一次华丽登场!

我们把公式改成:
Q Effect:=SUMX(VALUES(Product[product]), S0/Q0*(Q1-Q0))

VALUES的意思可以理解成删除重复值,所VALUES(product[product])的意思就是只保留Product唯一值,计算过程就是:
Picture4.png

目前就基本差不多了,但还有点小问题:
1.  1                  1.  如果上年没有销售怎么办?

根据公式,Q effect会变成0,所以要加一个if判断:
Q Effect:=SUMX(VALUES(Product[product]), if(Q0=0,S1,S0/Q0*(Q1-Q0)))
2.2       2.    如果今年没有销售怎么办?
其实这已经不是个问题啦,根据公式,Q effect就是上年的销售的负数,-S0,但有一点要注意,一定要是VALUES(Product[product]),而不能是VALUES(Transaction[product])!因为今年没有销售,也就是没有这个货物,SUMX就会直接跳过这个货物的计算,直接变成0。(和DATEADD有些像哦)

现在我们可以来比较一下Q effect 1(假的)和Q effect的分别啦!
Picture5.png

然后,就可以加上各种各样的FilterSlicer什么的进行分析啦!其反应速度和方便程度是Excel公式完全没有办法比拟的!各种实例如下:
Picture6.png
附件放到下边了,本菜鸟也随大溜升级成Excel 2013啦!PowerPivot可以由Excel 2010版本自动升级到2013版本,但这个过程是不可逆的,而且Excel2010也看不了2013下的PowerPivot

另外,如果你想使用2013 PowerPivot,购买单独版本的Excel 2013或者超级贵的Office 365企业版,之后无需其他下载,直接在Add-in里边导出来即可。而Office家庭版、学生版、专业版好像都不行。




深层次讨论:
1)优化公式
上边写的P&Q effect公式是有瑕疵的(这个是一个简便版),priceeffect被夸大了,更加符合逻辑的应该是这样:
Picture7.png
所以变成:
Q effect
= P0*(Q1-Q0)+ (P1-P0)*(Q1-Q0)/2
=(P1+P0)*(Q1-Q0)/2
= (S1/Q1+S0/Q0)*(Q1-Q0)/2

DAX公式变成
Q Effect:=SUMX(VALUES(product[product]),if(Q0=0, S1,(S1/Q1+S0/Q0)*(Q1-Q0)/2))

2)优化分析
上边的分析只能用来同上年同期比较,而如果你想和上个月,上个季度比较呢?
我们就要修改下S0Q0的表达了,即DDATE里边的参数。一张新的表格需要建立。
Picture9.png
Period:= iferror(VALUES(Interval[Interval]),”year”)(意思就是默认就是以年为单位)

S0Q0的公式也要改变:
S0:=SWITCH(TRUE(),
[Period]="year",CALCULATE([S1],DATEADD('Date'[DateKey],-1,YEAR)),
[Period]="quarter",CALCULATE([S1],DATEADD('Date'[DateKey],-1,quarter)),
[Period]="month",CALCULATE([S1],DATEADD('Date'[DateKey],-1,month))
)
很像VBA里边的CASE的用法(具体我也不清楚,但大概就这么用)

然后建立一个intervalSlicer。就可以随意选择了,图例如下:
Picture8.png
零零散散写了这些,感觉逻辑好跳步,不过算了。。。 P&Q effects.rar (141.67 KB, 下载次数: 337)

Picture9.png

评分

2

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-12-3 15:07 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-12-3 15:07 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2013-12-3 19:34 | 显示全部楼层
楼主这种方法值得学习,立此重誓来督促自己,希望楼主能在自定的期限能完成任务。

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-12-4 13:37 | 显示全部楼层
dajiahaoxinku12 发表于 2013-12-3 19:34
楼主这种方法值得学习,立此重誓来督促自己,希望楼主能在自定的期限能完成任务。

文档已写好,就差般过来啦!预计今晚完成!

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-12-4 23:03 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2013-12-6 21:31 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2014-10-20 21:26 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
楼主 请教个问题 ,如果几十万行的数据在powerpivot里创建数据透视表的话,怎么处理合适(分析的维度比较多),因为数据透视表还是在excel表里的,处理起来还是死机!

TA的精华主题

TA的得分主题

 楼主| 发表于 2014-11-19 17:18 | 显示全部楼层
gohrfang 发表于 2014-10-20 21:26
楼主 请教个问题 ,如果几十万行的数据在powerpivot里创建数据透视表的话,怎么处理合适(分析的维度比较多 ...

不好意思,好久没有来了,希望回复你还可以看到。。。

一般来说几十万行不会有问题的,但要看你用了什么DAX,比如SUMX之类的X DAX会非常占用系统资源,有可能导致死机,所以你可能需要优化一下编写的Measures。

如果你把大量的数据放在Excel的Pivot Table里,当然也会导致死机,毕竟一个Sheet只有100万行的容量,超过10万行就非常卡了。不过把这么多数据都展示出来不符合Power Pivot设计的初衷,所以可能需要提高一下展示的方法。

我没有数据其实很难给出更多的建议,不过Power Pivot的确在很多地方并不是很完善和友善,所以我已经“叛逃”去Tableau啦~

TA的精华主题

TA的得分主题

发表于 2014-12-19 17:19 | 显示全部楼层
tc0098 发表于 2014-11-19 17:18
不好意思,好久没有来了,希望回复你还可以看到。。。

一般来说几十万行不会有问题的,但要看你用了什 ...

感谢楼主看到了,还是给我耐心的回复了,前段时间没办法 只能去简单的学了下SQL SERVER 处理数据
感谢介绍Tableau这个新工具,去学习学习!
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 20:59 , Processed in 0.059069 second(s), 11 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表