ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

   
EH云课堂-专业的职场技能充电站 限时送,魔方网表将Excel变在线系统 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 高效办公必会的Office实战技巧 免费下载Excel行业应用视频
300集Office 2010微视频教程 Tableau-数据可视化工具 ExcelHome出品 - VBA代码宝免费下载 13门Excel免费公开课任你学
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 免费的Excel考勤计算系统
查看: 17193|回复: 31

[求助] 菜鸟PP周记(二)DAX之求增长率(Calculate,ALL,Filter)

[复制链接]

TA的精华主题

TA的得分主题

发表于 2013-7-6 00:22 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:Powerpivot
本帖最后由 tc0098 于 2013-7-7 14:38 编辑

度量值(measure)基于DAX公式,拥有极其强大的灵活性和计算能力。

做个简单的介绍,我们做一个Measure SalesValue:= sum(data[sales]),对Sales列求和。当你把这个Measure放到Pivot Table的计算中的时候,FilterColumnRow都会对这个Measure产生影响,原理和普通的Pivot完全一样的。但普通的Pivot是不可以放一个Measure的,只可以放一个列,然后规定它的计算方式是求和,平均什么的,可想而知限制就很多了,所以我们的Measure就可以在这里大放异彩了!
1.png
图1

这次主要来介绍一下Calculate这个DAX公式。
如果你喜欢SumifsAverageifsCountifs,那么一定会爱上Calculate这个公式的!原理很简单,就是一个计算式加一堆条件
                         =calculate(计算式,条件1,条件2,。。。,条件n)
举个简单的例子:2013 Sales=Calculatesum(data[sales]),data[year]=2013)。就是说计算2013年的的Sales,所以不管前边的条件怎么变,2011-2013“2013 sales”都是一个数。
2.png
图2

大家会问了:这有毛用?(举个例子而已,是没什么用。。。。不过里边有很重的概念的!)

好,接下来来有用的,大家接招吧!

目的:得到每一年的Gowrth Rate

这个够有挑战了吧!让我们一步一步来看Calculate如何完成这个任务的:

首先GrowthRate:=CY Sales/PY Sales-1, (今年的sales/去年的Sales-1),今年的Sales很简单,我们做Measure1SalesValue:=sum(data[sales]),轻松完成!(就是上边的图了)

然后最大的挑战就来了,去年的Sales怎么弄出来呢?我们这么想,在Pivot里边把Year拉到列上,就是告诉了Measure SalesValue,要2011, 2012和2013分别的Sales;我们想要上年的Sales,那就要告诉它,我们要前一年的Sales2010,2011和2012Sales,就是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.png
图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.png
图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())
5.png

就算把SalesValue和PY Sales去掉,Growth也照样可以出来!而且2011行自动消失了哦(因为没有2010的数据,所以算不出PY Sales,是个Error,返回Blank(),就消失啦!)
6.png

呼,总算解释完这个公式了!累死了。。。。

写写帖子还是很好的,之前其实只会用这个公式,但对很多地方都没有完全了解,直到写帖子想讲解的时候才发现原来还有很多不懂的地方,于是开始做实验,才发现了上边写的公式的原理。希望能对大家有用了!

忘记添加附件了。。。只有装有PowerPivot才能用啊!

2 Powerful Measure.rar (57.92 KB, 下载次数: 590)

评分

参与人数 7财富 +50 鲜花 +10 收起 理由
hugo_huang + 1 值得肯定
ivy_zying + 2 优秀作品
anthonii + 2 太强大了
hope9966 + 2 感谢帮助
libinghua_gz + 1 优秀作品

查看全部评分

TA的精华主题

TA的得分主题

发表于 2013-7-6 09:24 | 显示全部楼层
太强大了,我英文不过关,都看不懂……

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-7-7 14:36 | 显示全部楼层
凌空一羽 发表于 2013-7-6 09:24
太强大了,我英文不过关,都看不懂……

还好还好。。。现在PowerPivot用的人不多,不过我还感觉挺好用的,写写帖子督促一下自己学习,自娱自乐!

TA的精华主题

TA的得分主题

发表于 2013-7-28 22:45 | 显示全部楼层
LZ介绍的功能很强大啊!!  :)

请教一下楼主有用过 DATESQTD()  DATESYTD() 等 DAX公式吗?

TA的精华主题

TA的得分主题

发表于 2013-7-29 13:33 | 显示全部楼层

就是改变了数据的布局方式。看这个报表的人,得接受一定的培训,这样,才能正确理解报表。

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-7-30 16:58 | 显示全部楼层
masterexcel 发表于 2013-7-29 13:33
就是改变了数据的布局方式。看这个报表的人,得接受一定的培训,这样,才能正确理解报表。

看个增长率需要接受什么培训啊。。。

PP不是用来改变布局方式的,而是让KPI的计算更加快捷和方便的。不仅仅是增长率,其他KPI比如平均售价,销售每平米,销售每人,气温高于30度的销售,上架1年内产品销售什么的,用PP做比Excel要便捷更实用,比SAP做省钱省时间

TA的精华主题

TA的得分主题

发表于 2013-8-2 11:38 | 显示全部楼层
想学SPSS,ACCESS,SQL,VBA,数组公式,发现还是PowerPivot才是最适合我的。不想成为专业IT,但不想为数据所累。

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-8-2 16:00 | 显示全部楼层
liulonghui 发表于 2013-8-2 11:38
想学SPSS,ACCESS,SQL,VBA,数组公式,发现还是PowerPivot才是最适合我的。不想成为专业IT,但不想为数据所累 ...

哈哈,同道中人!自己动手,丰衣足食!

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-8-2 16:01 | 显示全部楼层
libinghua_gz 发表于 2013-7-28 22:45
LZ介绍的功能很强大啊!!  :)

请教一下楼主有用过 DATESQTD()  DATESYTD() 等 DAX公式吗?

这些日期的公式也很好用啊!可以考虑下期写一个篇关于PP强大日期计算的日志!

TA的精华主题

TA的得分主题

发表于 2015-3-11 22:58 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关闭

最新热点上一条 /1 下一条

关注官方微信,高效办公专列,每天发车

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

GMT+8, 2019-1-24 20:39 , Processed in 0.131778 second(s), 12 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 2001-2017 Wooffice Inc.

   

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

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

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