楼主
嘉昆2011 |
声明：
1，素材来源于下列网站博客：- www.powerpivotpro.com；
- dennyglee.com；
- blogs.office.com；
- technet.microsoft.com
2，案例中数据无实际意义。
本文概要：
PowerPivot 知识点初探：
1，名称集（Named Set）定义多维表达式（MDX）：TopCount
2，度量字段（Measure）定义数据分析表达式（DAX）：If, Min, Switch, RankX, Distinct
3，Cube函数
引例：
通常我们利用数据透视表统计销售量前10名的员工都要经过下图这个步骤：
如果要查看排名前5，15，20名的员工，就每每都要经过上述步骤，而且无法有针对性（即设定目标员工）。这时需要引入名称集这个概念（通常情况下，数据透视表选项下此项为灰色，为无应选状态，所以借助PowerPivot这个平台演示。）
名称集的好处是可以设定目标员工，相当于筛选功能（选出目标，本例暂不用）；制作不均衡报表，即不显示无用列字段（本例暂无用）；定义多维表达式。
 - TopCount
- (
- {[Table1].[Employee_ID].[Employee_ID].Members},
- 10,
- [Measures].[Sum of Total]
- )
TopCount用法详见：- http://technet.microsoft.com/en-us/library/ms144792.aspx
其目的是为得到销售量排名前10的员工集合。
或许，有人会问用SQL+数据透视表不能得到同样的效果吗？- Select B.*
- From
- (Select Top 10 Employee_ID, Total
- From [Data$]
- Order by Total Desc) A, [Data$] B
- Where A.Employee_ID=B.Employee_ID
- Order by B.Total Desc
SQL语句只能得到相对静态的筛选结果，暂时在Excel 2010版无法与切片器联动得到动态结果。
PowerPivot Pro利用Excel 2013 SQL+DAX隐藏功能＋录制宏实现了与切片器间的动态切换：- http://www.powerpivotpro.com/2012/07/implementing-a-dynamic-top-x-via-slicers-in-excel-2013-using-dax-queries-and-excel-macros/
为了能够达到动态选择排名前N的员工
建立辅助表，链接相关表，插入切片器，定义数据分析表达式
为了查看具体某个排名／产品的相关员工，定义最小度量值：- [Sel TopN Value]=Min(Table3[Top])
- [SelTopN]=Min(Table2[ID])
当产品切片器筛选变换时，根据产品代码返回相应的数据集合（联想下列表筛选功能就比较容易理解了）
方法比较多，这里用If在数据表的判别示和Switch选择来实现：- =IF([Product]="BISCUITS",[Total],0)
- [Sel TopN Item]=SWITCH([SelTopN], 1, DISTINCT(Table1[Biscuits]), 2, DISTINCT(Table1[Chocolates]), 3, DISTINCT(Table1[Cookies]), 4,DISTINCT(Table1[Juice]), 5,DISTINCT(Table1[Milk]))
用RankX来返回销售量排名- [EmployeeBySelection]=RANKX(ALL(Table1[Employee_ID]),[Sel TopN Item], ,0)
判断是否显示在排名N名后的员工- [EmployeeID Included or Not]=IF([EmployeeBySelection]<=[Sel TopN Value],1,0)
为了能更清晰地知道现在具体显示的是某产品销售量排名前N的员工，可以建立一张隐数据表来显示标题，可以
利用Cube函数，度量字段与切片器建立关联：- =CUBEVALUE("PowerPivot Data","[Measures].[Sel TopN Value]",Slicer_Top)
相关网站参考资料：- http://dennyglee.com/2010/01/27/create-a-powerpivot-report-filtered-by-the-top-x-users-by-logic-part-1-of-3/
- http://blogs.office.com/b/microsoft-excel/archive/2009/10/05/pivottable-named-sets-in-excel-2010.aspx
- http://www.powerpivotpro.com/2012/07/dynamic-topn-reports-via-slicers-part-2/
- http://technet.microsoft.com/en-us/library/ms144792.aspx
- http://www.powerpivotpro.com/2012/07/implementing-a-dynamic-top-x-via-slicers-in-excel-2013-using-dax-queries-and-excel-macros/
附件：
 TopN.rar
本文意在抛砖引玉，对PowerPivot一些基础功能与用法进行初探，欢迎交流相关用法与实例。
祝周末愉快。
|
