本帖最后由 tc0098 于 2013-12-4 23:02 编辑
前言：
唉，周记变月记，月记变季记，实在是懒的丧心病狂啊。。。为了督促自己，在此立贴为证，本周之内，完成通过PowerPivot解决Price & Quantity Effect Analysis的问题！如无法完成，注销账号！{:soso_e130:}
先做介绍一下定义吧，Sales = Price * Quantity (简写：S=P*Q)。 一个货物，上年卖了200，今年卖了275，想知道价钱和数量对销售增长的影响各有多大，就是按下表来计算： 数量的影响是50，价钱的影响是25。原理就像下边这个图一样啦。
如果货物只有一个的话，计算当然很简单啦。但如果一个公司有上千种货物，则需要计算每一个货物的P&Q Effect，然后再加起来才是整个公司的P&QEffect。
这个自然可以用万能的Excel计算，用Sumifs可以完成，但还是一样的道理Excel中Sumifs所占用的资源非常庞大，如果要计算几万行数据的P&QEffect，基本就是不断死机的节奏，而且如果想看和上个月比较，上个季度比较，不同店铺比较等等，写公式估计就要写死了。所以，PowerPivot的优势就在这里体现出来了。
好，接下来开始解题！
首先，建立表和表的关系！ 然后开始写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))
效果如图 公式的意思很明了吧，就是求目前的销售，但把日期推前一年（就是求上一年的销售啦）。
要注意的是，这里引用的日期列一定要是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唯一值，计算过程就是：
目前就基本差不多了，但还有点小问题： 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的分别啦！
然后，就可以加上各种各样的Filter、Slicer什么的进行分析啦！其反应速度和方便程度是Excel公式完全没有办法比拟的！各种实例如下： 附件放到下边了，本菜鸟也随大溜升级成Excel 2013啦！PowerPivot可以由Excel 2010版本自动升级到2013版本，但这个过程是不可逆的，而且Excel2010也看不了2013下的PowerPivot。
另外，如果你想使用2013 PowerPivot，购买单独版本的Excel 2013或者超级贵的Office 365企业版，之后无需其他下载，直接在Add-in里边导出来即可。而Office家庭版、学生版、专业版好像都不行。
深层次讨论： 1）优化公式 上边写的P&Q effect公式是有瑕疵的（这个是一个简便版），priceeffect被夸大了，更加符合逻辑的应该是这样： 所以变成： 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里边的参数。一张新的表格需要建立。 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。就可以随意选择了，图例如下：
| 
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2013-12-3 15:06
楼主
