利用PowerPivot和DAX进行数据建模与计算

### 利用Power Pivot和DAX进行数据建模与计算 #### 1. Power Pivot数据建模基础 在数据建模过程中，我们会用到Power Pivot这一强大工具。首先，我们可以创建层次结构，比如将其命名为“Location”，从层次结构的最高级别“Country”开始，依次将各列拖入“Location”层次结构，以此创建各级层次。创建好的层次结构可用于在数据透视表中深入查看度量值或数值。 当使用主键和外键在两个表之间创建关系时，数据透视表字段中会出现重复字段。例如，“Product ID”会同时出现在“Product”表和事实表中。为避免用户在使用切片器、过滤器或设置行、列字段时产生混淆，Power Pivot提供了隐藏字段和表的功能。具体操作如下： - **隐藏列**：在图表视图中，右键单击要隐藏的字段，选择“从客户端工具隐藏”。 - **隐藏表**：在图表视图中，点击表的标题，然后选择“从客户端工具隐藏”，这样该表将不会出现在数据透视表字段列表中。 #### 2. DAX计算基础 在完成数据建模后，我们需要计算一些关键指标来衡量销售绩效。在数据模型中，可使用DAX（Data Analysis Expressions）创建两种类型的计算：计算列和度量值。 ##### 2.1 计算列与度量值的区别 - **隐式度量值**：可通过直接将现有列或字段拖到数据透视表的“值”区域创建。 - **显式度量值**：使用公式创建，可在数据模型的任何部分访问和重用。显式度量值具有以下特点： - 可在数据模型中以多种方式重复使用。例如，计算某一特定产品的总销售额后，可在最终仪表板的多个可视化组件中使用该度量值。 - 可以为度量值应用永久的数字格式，在仪表板或报告中应用该度量值时将使用此格式。 - 可使用现有度量值创建其他复杂度量值。 - 在数据透视表中，显式度量值只能用于“值”区域。 ##### 2.2 创建第一个度量值 在创建第一个度量值之前，建议将所有度量值存储在一个表中，便于组织和管理。创建度量值表的步骤如下： 1. 选择工作表中的一个空单元格。 2. 复制该空单元格，点击“管理”按钮，打开Power Pivot窗口。 3. 在“主页”选项卡下点击“粘贴”，在弹出的“粘贴预览”对话框中为表指定新名称，如“My Measures”，然后点击“确定”。 接下来创建第一个度量值“Total Quantity”，步骤如下： 1. 点击Power Pivot选项卡中的“度量值” - “新建度量值”。 2. 在“度量值”对话框中，从下拉列表中选择“My Measures”表。 3. 在“度量值名称”中输入“Total Quantity”，可在“描述”字段中提供度量值的相关说明。 4. 在“公式”区域输入公式，如“=SUM('Main Transaction'[Quantity])”。注意，引用列时应使用完全限定名称“Table[Column]”，若表名包含空格，需用单引号括起来。 5. 点击“fx”按钮可探索DAX函数库，点击“检查公式”按钮验证公式的准确性，确认无误后点击“确定”。 要查找已创建的度量值，可点击Power Pivot选项卡中的“度量值” - “管理度量值”，在弹出的对话框中可进行添加、编辑或删除度量值的操作，也可在Power Pivot窗口中查看。 #### 3. 常见DAX函数 DAX函数有250多个，下面介绍一些常见的函数类别及其用途： |函数类别|用途|示例函数| | ---- | ---- | ---- | |聚合函数|计算列或表中所有行的标量值，如总和、平均值、最小值、最大值和计数|SUM、AVERAGE、MAX、MIN、COUNT| |筛选函数|协助获取特定数据类型，在相关表中搜索值，并根据关联值应用筛选器|CALCULATE、FILTER、ALL、SELECTEDVALUE| |财务函数|用于财务计算，如净现值（NPV）和回报率（RoR）|PMT、FV、IPMT| |信息函数|检查作为参数传递给其他函数的表或列，确定值是否符合预期类型|ISERROR| |逻辑函数|提供表达式中值的详细信息|IF、