本帖最后由 tc0098 于 2013-7-7 14:38 编辑
度量值（measure）基于DAX公式，拥有极其强大的灵活性和计算能力。
做个简单的介绍，我们做一个Measure， SalesValue:= sum(data[sales])，对Sales列求和。当你把这个Measure放到Pivot Table的计算中的时候，Filter，Column和Row都会对这个Measure产生影响，原理和普通的Pivot完全一样的。但普通的Pivot是不可以放一个Measure的，只可以放一个列，然后规定它的计算方式是求和，平均什么的，可想而知限制就很多了，所以我们的Measure就可以在这里大放异彩了！ 图1
这次主要来介绍一下Calculate这个DAX公式。 如果你喜欢Sumifs，Averageifs，Countifs，那么一定会爱上Calculate这个公式的！原理很简单，就是一个计算式加一堆条件： =calculate(计算式，条件1，条件2，。。。，条件n) 举个简单的例子：2013 Sales=Calculate（sum(data[sales]),data[year]=2013）。就是说计算2013年的的Sales，所以不管前边的条件怎么变，2011-2013的“2013 sales”都是一个数。 图2
大家会问了：这有毛用？（举个例子而已，是没什么用。。。。不过里边有很重的概念的！）
好，接下来来有用的，大家接招吧！
目的：得到每一年的Gowrth Rate！
这个够有挑战了吧！让我们一步一步来看Calculate如何完成这个任务的：
首先GrowthRate:=CY Sales/PY Sales-1, （今年的sales/去年的Sales-1），今年的Sales很简单，我们做Measure1，SalesValue:=sum(data[sales])，轻松完成！(就是上边的图了）
然后最大的挑战就来了，去年的Sales怎么弄出来呢？我们这么想，在Pivot里边把Year拉到列上，就是告诉了Measure SalesValue，要2011, 2012和2013分别的Sales；我们想要上年的Sales，那就要告诉它，我们要前一年的Sales，2010，2011和2012的Sales，就是Year=Year-1 （好像VBA的感觉！）。来，让我们一步一步学习怎么告诉PowerPovit：
1. 先写一个过渡的Measure： ALL Sales:= CALCULATE([SalesValue],ALL(data[year])) （这里大家可以看到，计算式不仅仅可以是个公式，也可以是其他的一个Measure！）
这里的条件是ALL(data[year]) ALL的意思很简单，就是把一个表里边所有的筛选（filter）取消掉，返回一张没有筛选的表。
比如我写个Dax，ALL Sales 2:=calculate([SalesValue], All(data)),得到的效果就是不管Pivot表里边怎么筛选（年份也好，产品也好，什么也好），得出来的数据就是所有Sales的总和。
这里用ALL(data[year])的意思呢，就是只有Year这一个列的筛选我不想要，其他产品，顾客什么的，我还是要滴。所以意思就是得到所有年份Sales的总和。
下图里边大家就可以比较出ALL(data[year])和ALL(data)的区别了吧！ 图3
不过这和上年的Sales有什么关系呢？让我们继续！
2. 上第二个条件吧！ =CALCULATE([SalesValue],ALL(data[year]),FILTER(all(data[year]),data[year]=VALUES(data[year])-1)) 又是一个新DAX，Filter。
Filter是和Calculate绝配的DAX，能设置复杂的条件要求。它的格式是： =Filter（表或列，条件）
比如之前2013 Sale的例子，你也可以写成2013 Sales 2:=calculate([SalesValue],filter(data,data[year]=2013))。但像这种简单的条件就不用Filter出马了。（其实不是完全等价的，这里卖个关子：））
回到这个例子，"表"就是All(data[year]) （为什么又加ALL呢？卖第二个关子！），条件是data[year]=VALUES(data[year])-1，这就是传说中的Year=Year-1啦。
Data[year]指的是我们原始Data表中的Year列，而Values[data[year]指的是Pivot Table中的Year。比如Pivot中是2012，那就是告诉Measure，你要去找Data表里所有(2012-1=)2011的Sales。
而为什么Pivot的Year要加一个Values呢？本菜鸟尽量说一下自己的理解：
Data[year]指的是一个列，里边有很多很多的数据。比如之前2013 Sales的例子，Data[year]=2013，你是告诉找到这个列的所有等于2013的Year的行，注意，列=数值，多对单的关系。
这个例子里，同样，你要告诉data[year]取某个数值，但你不能说：data[year]=data[year]，列=列，多对多的关系，这样Measure就完全晕了，直接返回一个错误。所以右边一定是一个单一的值，Values的意思就是这样了，会返回一个列的所有唯一值。这个例子里呢，就是返回{2011,2012,2013}总共三个值。
但又要注意了，Pivot里边已经把Year放在了列里边，所以在Pivot中，2011行只会返回{2011}，2012行只会返回{2012}，2013行只会返回{2013}。（这里的Data[year]就千万不要加ALL了，要不出来就是{2011,2012,2013}三个值了）
但是如果你没有把Year列放在Pivot的列中，Values返回来的就是{2011,2012,2013}。Data[year]={2011,2012,2013}就是可空集了（没有一个行即等于2011，又等于2012，还等于2013吧？）。
3. 其实这时候大部分的问题已经解决了，我们来说一下之前卖的关子，FILTER(all(data[year]),data[year]=VALUES(data[year])-1)中的all(data[year])为什么要加ALL
其实原理有二：
首先，Calculate里边的所有条件的级别都是一样的，不是说条件1摆在前边就会对条件2产生限制，所以即便条件1已经定义了ALL(data[year])，但Filter里边的Data[year]还是被Pivot筛选过后的Data[year]，所以ALL必须加上。我们可以把ALL和Filter这两个条件调换一下，你会发现结果完全一样的。
然后问题又来了，既然Filter里边的Data[year]已经有ALL了，为什么还需要条件1的ALL(data[year])呢？其实这是关于Filter的特殊属性的，“只减不加”！什么意思呢？
如果条件是Data[year]=2013,这种不是Filter的条件，会粗暴地强行改变Pivot已经设置的条件的，所以在2013 Sales的例子里，2011-2013都只会显示2013的Sales。 而Filter呢，如果你写成 2013 Sales 2:=calculate([SalesValue],filter(data,data[year]=2013))
你会看到2011，2012是没有数的，因为Filter不会增加或者强行改变Pivot中已经设置的条件的，只会在其基础上做减少。 图4 所以，既然Pivot已经做了年份的筛选，想要做到前一年的Sales，只能先把Data表用ALL把年份的筛选给去掉，然后再用Filter弄成前一年的筛选条件，只减不加就体现在这里。
4. 最后，DAX公式的完整版是： PY Sales:= IF(HASONEVALUE(data[year]), CALCULATE([SalesValue],ALL(data[year]),FILTER(all(data[year]),data[year]=VALUES(data[year])-1)), BLANK()) 加了一个IF，请直译，如果Data[year]只有一个值（HAS ONE VALUE），那么咱们来算上年的Sales，否则就Blank。HASONEVALUE完全等价于Count(Values(data[year]))=1。Blank()相当于Excel里的””。 所以如果Pivot里边不定义年份，那就是有多个年份了，公式就会返回一个Blank，就不出现啦。
5. 最后的Growth Rate也就水到渠成啦： Growth Rate = Iferror([SalesValue]/[PY Sales]-1,Blank())
就算把SalesValue和PY Sales去掉，Growth也照样可以出来！而且2011行自动消失了哦（因为没有2010的数据，所以算不出PY Sales，是个Error，返回Blank()，就消失啦！）
呼，总算解释完这个公式了！累死了。。。。
写写帖子还是很好的，之前其实只会用这个公式，但对很多地方都没有完全了解，直到写帖子想讲解的时候才发现原来还有很多不懂的地方，于是开始做实验，才发现了上边写的公式的原理。希望能对大家有用了！
忘记添加附件了。。。只有装有PowerPivot才能用啊！
| 
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2013-7-6 00:22
楼主
