1.1 从PivotTable到PowerPivot！在外观上，PowerPivot与Excel传统数据透视表（英文称作PivotTable）似乎没什么明显区别，甚至连创建过程都基本相似，那为什么你做出来的就是普通的传统Excel数据透视表，而我做出来却是功能强大的PowerPivot超级数据透视表呢？秘诀就在于“创建数据透视表”对话框里的一个关键设置：将此数据添加到数据模型！
我们在创建传统Excel数据透视表时，过程是这样的：选择数据源，依次点击“数据》插入》数据透视表”按钮，会弹出一个“创建数据透视表”对话框，就是在这个对话框下方，也许你从未留意，有这样一个设置：“将此数据添加到数据模型”。
在默认情况下，这个选项是不被勾选的。在不勾选这个选项的情况下，点击创建数据透视表对话框最下方的“确定”按钮后，生成的Excel数据透视表就是普通的、功能有限的传统Excel数据透视表。而当勾选“将此数据添加到数据模型”时，插入到Excel中的数据透视表就变成了PowerPivot，也就是功能强大的“超级数据透视表”。
尽管在外观上，超级数据透视表PowerPivot和传统Excel数据透视表并没有什么明显的区别，但是，因为勾选了“将此数据添加到数据模型”，PowerPivot将与传统数据透视表的内在生成方式上完全不同，两者的能力也会有天壤之别！
下图中，PowerPivot值区域中计算的是图书销售册数的“非重复计数”，而非总销售册数的合计。非重复计数这个功能，在传统Excel数据透视表中是没有的，而“非重复计数”这个能力，相对于PowerPivot所有能力，只是“小荷才露尖尖角”。
回到本节的主题，为什么当我们勾选了“创建数据透视表”对话框中的“将此数据添加到数据模型”后，会让数据透视表忽然具有如此神奇的功能呢？
其实，当你勾选“将此数据添加到数据模型”这个选项时，Excel会把数据源中的数据先加载到PowerPivot专用数据库里，正式因为这个PowerPivot专用的数据库，让我们有机会在数据最终展示到数据透视表之前，利用PowerPivot数据模型管理界面对数据进行各种自定义的、灵活操作。
要想查看PowerPivot背后的数据模型（或者说PowerPivot专用数据库），请点击“Power Pivot》数据模型”按钮，这时，进入到PowerPivot数据模型管理界面。
注意：如果你的Excel界面上没有PowerPivot菜单，则需在Excel中做如下设置：点击Excel“文件》选项》加载项”，在“加载项”对话框下方的“管理”下拉框中选择“COM加载项”，然后点击旁边的“转到”按钮，这时，在接下来弹出的“COM加载项”对话框中勾选“Microsoft Power Pivot for Excel”，然后再点击“确定”按钮即可。
在PowerPivot数据模型管理界面中，我们看到，该界面分成上中下三部分。最上方是数据模型管理界面的菜单区，中间部分是我们刚刚导入到模型中的数据，下方的网格则是DAX数据分析表达式编辑区，这里也是我们写入各种超级强大的数据分析表达式(DAX)的地方。
在PowerPivot数据模型管理界面左下角，我们看到，我们刚刚导入的表格被PowerPivot数据模型管理器自动命名为“区域”，而不是默认使用Excel工作表的名称，如果不喜欢这个名称，我们可以右键更改它，但在这里，我们使用这个默认表格名称。
在PowerPivot数据模型管理界面，我们可以对来自数据源的数据进行初步的观察和研究。在分析数据之前，对数据进行初步的观察和研究，这是非常必要的，如果对数据都不了解，何谈分析！
在数据模型管理界面的数据表格中，我们可以对数据进行筛选和增加新列等操作，增加新的列在PowerPivot术语中叫做增加“计算列”。
在下方的DAX表达式编辑区，我们可以把实现各种复杂数据分析运算的DAX数据分析表达式写在里面。PowerPivot数据模型管理界面相当于PowerPivot数据模型的控制面板，是我们发挥聪明才智，调取PowerPivot强大能力的地方。
这里需要提示的是：我们在PowerPivot模型管理界面中的筛选操作的结果并不会传递到由此生成的超级数据透视表中去。PowerPivot模型管理界面中的筛选只是用来观察后台数据和初步调试DAX表达式的地方，PowerPivot模型管理界面里的筛选操作和由此生成的PowerPivot是之间是没有任何联系的。 1.2 PowerPivot中的自定义运算我们先从一个最简单的DAX数据分析表达式开始：我们将利用PowerPivot和DAX数据分析表达式，实现一个传统Excel数据透视表中已有的功能，即利用DAX数据分析表达式中的SUM()函数对图书销售册数进行汇总。
首先，我们创建一个PowerPivot，此刻，别忘了在插入数据透视表的“创建数据透视表”对话框中，勾选“将此数据添加到数据模型”选项！这是生成PowerPivot的关键一步。接下来，点击“Power Pivot》数据模型”按钮，进入PowerPivot的数据模型管理界面。
在PowerPivot数据模型管理界面，我们看到，数据已经加载到了PowerPivot数据管理模型。这里再强调一下，如果在创建数据透视表时，你不勾选“将此数据添加到数据模型”选项的话，在这里是看不到任何数据的。
你可能已经注意到，PowerPivot数据模型里的数据源表名称并不是工作表的名称，而是被自动重新命名为“区域”，这个名称可以右键点击名称标签自行修改。
PowerPivot数据模型里的数据和数据源是动态链接的，也就是说，如果数据源中的数据发生了变化，我们点击界面菜单上的“刷新”按钮，数据模型界面中的数据会跟着发生变化。
下面，我们在PowerPivot数据模型管理界面下方任意一个网格中输入如下DAX数据分析表达式：=SUM('区域'[T3销售册数])。这里你会发现，当我们输入“=SUM(”之后的上撇号“’”时，PowerPivot会提示可用的数据模型中字段名称，这大大加快了我们键入DAX数据分析表达式的速度，请大家充分利用这个特性。
当我们完成公式输入后，PowerPivot会自动给DAX表达式起一个默认的名字“度量值1”。这里需要大家记住：在PowerPivot中，术语上把写在PowerPivot数据模型管理界面下方网格中、起到汇总计算作用的DAX数据分析表达式，叫做“度量值表达式”或者叫做“度量值”。度量值将会放置在PowerPivot超级数据透视表的值区域中。
度量值表达式的名称（即DAX表达式冒号前面的部分），我们可以自行定义，为了容易理解，这里我们把“度量值1”改成“my总册数”。
我们看到，这个度量值表达式相当简单，凭借对Excel工作表函数的使用经验，我们推测出公式=SUM('区域'[T3销售册数])的作用是对数据源表格中的[T3销售册数]字段进行累加操作。这相当于在数据透视表中增加了一个新的“值”字段（即可以拖拽到PowerPivot值区域中的字段）。下面，我们就来看看把这个度量值添加到数据透视表值区域中是什么效果。请点击PowerPivot数据管理界面菜单中的“数据透视表”按钮。
现在我们进入了数据透视表（PowerPivot）界面。在数据透视表字段布局视图中拖拽字段，生成下图所示的数据透视表。此时，在工作表右侧的数据透视表字段列表中，我们看到了一个新的名称“my总册数”，这个就是我们刚刚在PowerPivot数据模型管理界面中设计的DAX度量值表达式。该度量值字段现在已经被拖拽到了数据透视表的值区域中。
我们发现，数据透视表的值区域中的数值和我们直接把数据源中的字段[T3销售册数]拖拽到数据透视表值区域中的默认求和结果一致。
请大家注意，这里虽然只是一个最简单的PowerPivot度量值表达式，但它留给我们的想象空间却是巨大的。在传统Excel数据透视表中，值区域中的汇总方式只能在有限的几种方式中选择，而有了PowerPivot，我们则可以用DAX数据分析标大赛中的函数设计自己的数据汇总方式！
虽然在这个例子中我们只使用了最简单的自定的汇总方式SUM()，但事实上，这里的DAX数据分析表达式可以是其他任何有意义的DAX公式，因此，我们可以通过设计不同的DAX表达式，让PowerPivot的汇总能力只局限于我们对DAX数据分析表达式应用的掌握程度！ 1.3 PowerPivot计算列与度量值我们已经介绍过，PowerPivot数据模型管理界面下方的网格，是添加自定义DAX数据分析表达式的地方，我们把写在这里的DAX数据分析表达式叫做“度量值”。所谓度量值，实际上可以简单理解为“一种自定义的数据透视表值区域汇总方式”。
当PowerPivot的数据源为单个表格时，DAX度量值表达式可以写在DAX网格的任意位置，度量值表达式与其所在的行或者列的位置无关，只要度量值引用了数据源表格中正确的字段即可。
在传统Excel数据透视表中，如果数据源的某个字段是数字类型，那么当我们把这个字段拖拽到数据透视表的值区域时，默认的汇总方式总是求和。在PowerPivot超级数据透视表中，我们可以设计DAX数据分析表达式模拟传统数据透视表的这个行为，就像刚刚介绍的那个DAX表达式SUM()函数一样。
在PowerPivot数据模型管理界面中，能够写入DAX数据分析表达式的地方有两个：除了在下方的DAX表达式设计网格中写入“度量值”公式外；我们还可以在PowerPivot数据模型管理界面中数据表格的最右侧，增加新的DAX“计算列”公式。
在PowerPivot中，我们把在数据表中增新加的列叫做“计算列”。计算列字段一般用在PowerPivot数据透视表筛选环境字段中（比如行标题、列标题、切片器、筛选字段等）。
下图中，我们为数据表增加了一个新的计算列，用来生成图书大类代码。具体操作方法是，在数据表最后的空白列中任选一个单元格，键入公式：=LEFT('区域'[T0大类K],1)，按下回车键便得到了结果。
这里我们看到，这个DAX函数和Excel工作表函数名称和用法基本相同，这是一个好消息。在DAX中，很多简单函数与Excel工作表函数名称和用法基本相同。
计算列的默认标题一般是“计算列+序号”，为了让计算列的名字更加友好，我们可以双击列名，把默认列名修改为自己喜欢的名字，比如这里，我们把计算列的名称更改为“大类代码”。
1.4 最重要的函数：CALCULATE()前面介绍了在PowerPivot值区域中自定义汇总方式的基本方法，即设计DAX度量值公式。接下来，我们将用DAX实现更加灵活的PowerPivot值区域汇总方式。利用这种方式，我们几乎可以在PowerPivot值区域内实现任何复杂的汇总分析！
为实现这个在PowerPivot值区域中实现任何复杂汇总分析的目的，让我们隆重介绍一个非常非常（两个非常）重要的DAX数据分析表达式函数：CALCULATE()！
我们知道，透视表值区域每一个单元格中的内容虽然看起来只是一个简单的数值，但每一个数值背后，都对应着一个由该单元格所处的透视表筛选环境下的数据源子集，该数值便是在该数据源子集的基础上汇总而来的。
换句话说就是：数据透视表值区域中每一个单元格中的数值，都是由它对应的数据源子集，经过特定的某种汇总计算的结果。
由此我们推想：如果有一种方法，能根据实际需求，修改透视表值区域单元格所对应的筛选环境，从而得到数据源的任意子集的汇总值，那么，我们还有什么做不到的事情？！没错，DAX数据分析表达式就是来干这个的！而在DAX数据分析表达式中，最最重要的函数就是CALCULATE()！
在DAX中，CALCULATE()是一个汇总运算函数，该函数的特别之处在于，CALCULATE()在执行汇总运算之前，不但能够“识别”其当前所处的透视表筛选环境，而且，还能对其所处的透视表筛选环境进行“修改”。
事实上，CALCULATE()的汇总计算是在新的“叠加的透视表筛选环境下”进行的。该函数通常使用两个参数，第一个参数为“自定义的汇总运算”，第二个参数为“对当前筛选环境的修改”，其语法格式为：
=CALCULATE( 自定义的汇总运算, 对当前筛选环境的修改 )
CALCULATE()函数的语法看起来似乎并不复杂，但是，这里需要强调重要的一点，那就是CALCUCATE()函数的内部运算逻辑：
CALCULATE()函数的内部运算逻辑为：先对当前筛选环境进行的修改，然后再执行自定义的汇总运算。
我们之所以如此强调CALCULATE()函数的内部运算逻辑，是因为它的内部运算逻辑与参数的出场顺序是相反的！这与我们熟知的Excel工作表函数的工作方式完全不同，请大家一定留意！
CALCULATE()函数执行计算时，先由第2个参数开始。在CALCULATE()中，第二个参数被称为“筛选器”参数，因为他们的作用是修改CALCULATE()函数所处的当前筛选环境；CALCULATE()函数的第一个参数被称为“汇总参数”，因为第一个参数的作用是执行汇总计算。
在最简单的CALCULATE()函数应用中，第2个参数可以省略。在只有一个汇总参数的情况下，CALCULATE()函数将默认接受当前筛选环境，即，如果CALCULATE()函数用在数据透视表值区域中，则CALCULATE()函数将在其当前所处的透视表筛选环境下运算。
让我们从最简单的情况开始，研究只有第一个参数时的CALCULATE()函数的应用。比如前面介绍的度量值公式：=SUM('区域'[T3销售册数])，现在，我们在这个度量值公式外面套上一个CALCULATE()函数，并将新的DAX度量值公式命名为“总册数CALCU”，如下图。
我们回到数据透视表界面，把新设计的度量值拖拽到透视表值区域，得到如下结果。我们看到，以下两个度量值的计算结果并没有什么不同。
总册数CALCU:=CALCULATE(SUM('区域'[T3销售册数]))
my总册数:=SUM('区域'[T3销售册数])
以上DAX度量值公式的计算结果之所以完全相同，这是因为在PowerPivot中有这么一个特性：
当一个DAX度量值公式拖入到数据透视表值区域时，会隐含地在该度量值公式外面包裹一个看不见的CALCULATE()函数。
因此度量值表达式：
my总册数:=SUM('区域'[T3销售册数])
在被拖入到数据透视表值区域中时，隐含地，其公式外面会自动包裹一个CALCULATE()函数，也就是说，上述度量值公式被拖入PowerPivot值区域时，DAX公式会变成：
my总册数:=CALCULATE(SUM('区域'[T3销售册数]))
按照这个规律，我们刚刚设计的新度量值公式：
总册数CALCU:=CALCULATE(SUM('区域'[T3销售册数]))
在被拖拽到数据PowerPivot透视表值区域中时，也会包裹一个CALCULATE()函数，变成：
总册数CALCU:=CALCULATE(CALCULATE(SUM('区域'[T3销售册数])))
其效果相当于只包裹了一个CALCULATE()函数。因此，这两个度量值公式得到了同样的结果。
事实上，CALCULATE()函数在应用中很少只使用一个参数，因为这并不能发挥出它最大的运算能力。如果只用一个参数，作为度量值公式，那么还不如不使用这个函数，直接把CALCULATE()函数的第一个参数（汇总参数）拖拽到PowerPivot数据透视表值区域中更为省事，反正默认地，当DAX度量值公式在拖拽到PowerPivot值区域时，也会在外面包裹一个CALCULATE()。
本书接下来会频繁地使用带有两个参数的CALCULATE()函数，正是因为CALCULATE()函数的第二个参数，也就是所谓的“筛选器参数”，能够修改CALCULATE()函数所处的当前筛选环境，从而使得CALCULATE()函数能够在新的筛选坏境中实现各种灵活的汇总计算。
|
|
|
