查看: 1534|回复: 9|
在线时间115 小时经验129 威望0 性别保密最后登录注册时间阅读权限20UID1684828积分129帖子精华0分享0
EH初级, 积分 129, 距离下一级还需 221 积分
积分排行3000+帖子精华0微积分0
本帖已被收录到知识树中，索引项：
本帖最后由 tc0098 于
23:02 编辑
唉，周记变月记，月记变季记，实在是懒的丧心病狂啊。。。为了督促自己，在此立贴为证，本周之内，完成通过PowerPivot解决Price & Quantity Effect Analysis的问题！如无法完成，注销账号！
先做介绍一下定义吧，Sales = Price * Quantity (简写：S=P*Q)。一个货物，上年卖了200，今年卖了275，想知道价钱和数量对销售增长的影响各有多大，就是按下表来计算：
22:54 上传
数量的影响是50，价钱的影响是25。原理就像下边这个图一样啦。
22:54 上传
如果货物只有一个的话，计算当然很简单啦。但如果一个公司有上千种货物，则需要计算每一个货物的P&Q Effect，然后再加起来才是整个公司的P&QEffect。
这个自然可以用万能的Excel计算，用Sumifs可以完成，但还是一样的道理Excel中Sumifs所占用的资源非常庞大，如果要计算几万行数据的P&QEffect，基本就是不断死机的节奏，而且如果想看和上个月比较，上个季度比较，不同店铺比较等等，写公式估计就要写死了。所以，PowerPivot的优势就在这里体现出来了。
好，接下来开始解题！
首先，建立表和表的关系！
22:54 上传
然后开始写DAX公式！
我们先对公式进行一下小小的变换：
Q Effect:=P0*(Q1-Q0) = S0/Q0*(Q1-Q0)& &(因为我们价格的信息是销售除以数量出来的)P Effect:= (S1-S0)- Q effect& &(能简单就简单！)
然后就是如何计算出每个量啦（S1,Q1,S0,Q0）。
S1和Q1都是今年的销售量和销售数量，很好用DAX公式表达：S1:=sum(transaction[SalesValue])Q1:=sum(transaction[SalesQty])
S0和Q0稍稍麻烦一些，需要用到DATEADD的DAX公式。
PowerPivot拥有一套非常强大的日期计算逻辑，不同于Excel里边把1900年1月1日当作1，然后不断累积的逻辑，PowerPivot直接把日期当作日期来看了，具体是怎么样呢？我也不清楚，反正就是比Excel高级很多就是了。逻辑上的进步当然也带来了公式上的创新，各种关于日期的DAX公式可以方便的满足几乎所有的需求，DATEADD就是其中一个很好用的公式。
DATEADD逻辑很简单，就是把日期提前一段，可以是年、季度、月、日什么的。如果大家还记得第二篇日志关于如何求上年销售增长率（应该没人记得了。。。），要用到All，Filter等高级DAX和复杂逻辑，DATEADD则把一切变得简单粗暴了！S0: =Calculate([S1],DATEADD(Date[DateKey],-1,Year))Q0: =Calculate([Q1], DATEADD(Date[DateKey],-1,Year))
22:54 上传
公式的意思很明了吧，就是求目前的销售，但把日期推前一年（就是求上一年的销售啦）。
要注意的是，这里引用的日期列一定要是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唯一值，计算过程就是：
22:54 上传
目前就基本差不多了，但还有点小问题：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的分别啦！
22:54 上传
然后，就可以加上各种各样的Filter、Slicer什么的进行分析啦！其反应速度和方便程度是Excel公式完全没有办法比拟的！各种实例如下：
22:54 上传
附件放到下边了，本菜鸟也随大溜升级成Excel 2013啦！PowerPivot可以由Excel 2010版本自动升级到2013版本，但这个过程是不可逆的，而且Excel2010也看不了2013下的PowerPivot。
另外，如果你想使用2013 PowerPivot，购买单独版本的Excel 2013或者超级贵的Office 365企业版，之后无需其他下载，直接在Add-in里边导出来即可。而Office家庭版、学生版、专业版好像都不行。
深层次讨论：1）优化公式上边写的P&Q effect公式是有瑕疵的（这个是一个简便版），priceeffect被夸大了，更加符合逻辑的应该是这样：
22:54 上传
所以变成：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）优化分析上边的分析只能用来同上年同期比较，而如果你想和上个月，上个季度比较呢？我们就要修改下S0和Q0的表达了，即DDATE里边的参数。一张新的表格需要建立。
22:58 上传
Period:= iferror(VALUES(Interval[Interval]),”year”)(意思就是默认就是以年为单位)
S0和Q0的公式也要改变：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的用法（具体我也不清楚，但大概就这么用）
然后建立一个interval的Slicer。就可以随意选择了，图例如下：
22:54 上传
零零散散写了这些，感觉逻辑好跳步，不过算了。。。
(141.67 KB, 下载次数: 72)
23:01 上传
下载次数: 72
(3.87 KB, 下载次数: 1)
22:58 上传
下载次数: 1
本帖评分记录鲜花
总评分:&鲜花 + 4&
在线时间115 小时经验129 威望0 性别保密最后登录注册时间阅读权限20UID1684828积分129帖子精华0分享0
EH初级, 积分 129, 距离下一级还需 221 积分
积分排行3000+帖子精华0微积分0
占楼！（DDATE）
在线时间115 小时经验129 威望0 性别保密最后登录注册时间阅读权限20UID1684828积分129帖子精华0分享0
EH初级, 积分 129, 距离下一级还需 221 积分
积分排行3000+帖子精华0微积分0
占楼！（SUMX）
在线时间4022 小时经验14750 威望10 最后登录注册时间阅读权限100UID2034187积分14950帖子精华0分享0
积分排行27帖子精华0微积分0
楼主这种方法值得学习，立此重誓来督促自己，希望楼主能在自定的期限能完成任务。
在线时间115 小时经验129 威望0 性别保密最后登录注册时间阅读权限20UID1684828积分129帖子精华0分享0
EH初级, 积分 129, 距离下一级还需 221 积分
积分排行3000+帖子精华0微积分0
dajiahaoxinku12 发表于
楼主这种方法值得学习，立此重誓来督促自己，希望楼主能在自定的期限能完成任务。
文档已写好，就差般过来啦！预计今晚完成！
在线时间115 小时经验129 威望0 性别保密最后登录注册时间阅读权限20UID1684828积分129帖子精华0分享0
EH初级, 积分 129, 距离下一级还需 221 积分
积分排行3000+帖子精华0微积分0
写完啦！！！
在线时间1069 小时经验97 威望0 性别保密最后登录注册时间阅读权限20UID1302528积分97帖子精华0分享0
EH初级, 积分 97, 距离下一级还需 253 积分
积分排行3000+帖子精华0微积分0
下载下来，慢慢地消化。
在线时间260 小时经验29 威望0 性别保密最后登录注册时间阅读权限20UID852412积分29帖子精华0分享0
EH初级, 积分 29, 距离下一级还需 321 积分
积分排行3000+帖子精华0微积分0
楼主 请教个问题 ，如果几十万行的数据在powerpivot里创建数据透视表的话，怎么处理合适（分析的维度比较多），因为数据透视表还是在excel表里的，处理起来还是死机！
在线时间115 小时经验129 威望0 性别保密最后登录注册时间阅读权限20UID1684828积分129帖子精华0分享0
EH初级, 积分 129, 距离下一级还需 221 积分
积分排行3000+帖子精华0微积分0
gohrfang 发表于
楼主 请教个问题 ，如果几十万行的数据在powerpivot里创建数据透视表的话，怎么处理合适（分析的维度比较多 ...
不好意思，好久没有来了，希望回复你还可以看到。。。
一般来说几十万行不会有问题的，但要看你用了什么DAX，比如SUMX之类的X DAX会非常占用系统资源，有可能导致死机，所以你可能需要优化一下编写的Measures。
如果你把大量的数据放在Excel的Pivot Table里，当然也会导致死机，毕竟一个Sheet只有100万行的容量，超过10万行就非常卡了。不过把这么多数据都展示出来不符合Power Pivot设计的初衷，所以可能需要提高一下展示的方法。
我没有数据其实很难给出更多的建议，不过Power Pivot的确在很多地方并不是很完善和友善，所以我已经“叛逃”去Tableau啦～
在线时间260 小时经验29 威望0 性别保密最后登录注册时间阅读权限20UID852412积分29帖子精华0分享0
EH初级, 积分 29, 距离下一级还需 321 积分
积分排行3000+帖子精华0微积分0
tc0098 发表于
不好意思，好久没有来了，希望回复你还可以看到。。。
一般来说几十万行不会有问题的，但要看你用了什 ...
感谢楼主看到了，还是给我耐心的回复了，前段时间没办法 只能去简单的学了下SQL SERVER 处理数据
感谢介绍Tableau这个新工具，去学习学习！
积分≥4700即可申请
最佳会员奖章No.1
金牌优秀会员
金牌优秀会员奖章No.1
优秀会员奖章No.2
优秀会员奖章No.1
关注我们，与您相约微信公众平台！
Copyright 1999 - 2017 Excel Home. All Rights Reserved.本论坛言论纯属发表者个人意见，任何违反国家相关法律的言论，本站将协助国家相关部门追究发言者责任！
Powered by
本站特聘法律顾问：徐怀玉律师 李志群律师 &&官方微博：
[切换城市]
当前位置： >
> 医学检验实习报告心得
版权所有 粵ICP备号
联系电话：020-
、小时值班电话：
（）&——&千里马校园网，专业的大学生网站！
合作伙伴：&实习周记_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
评价文档：
12页免费5页免费1页免费3页¥1.001页免费12页1下载券7页免费6页3下载券1页2下载券4页1下载券
喜欢此文档的还喜欢4页4下载券18页2下载券10页免费5页1下载券28页7下载券
把文档贴到Blog、BBS或个人站等：
普通尺寸(450*500pix)
较大尺寸(630*500pix)
你可能喜欢}