使用DAX进行数据建模 - 快速指南

使用DAX进行数据建模 - 概述

所有组织的决策者都认识到需要分析其组织的具体历史数据以及整个行业的总体历史数据。在当今竞争激烈的世界中，这正日益变得至关重要，以应对不断变化的业务挑战。

大数据和商业智能已成为商业世界的流行词。数据源已变得庞大，数据格式也多种多样。当务之急是拥有易于使用的工具来处理不断涌入的大量数据，以便在更短的时间内获得洞察力，并在适当的时间做出相关决策。

数据分析师不再需要等待IT部门处理所需的数据。他们需要一个方便的工具，使他们能够快速理解所需的数据，并将其以有助于决策者在正确的时间采取所需行动的格式提供。

Microsoft Excel拥有一个强大的工具，称为Power Pivot，在早期版本的Excel中作为加载项提供，并在Excel 2016中作为内置功能提供。Power Pivot的数据库，称为数据模型，以及在数据模型上运行的公式语言，称为DAX（**D**ata **A**nalysis **E**xpressions），使Excel用户能够立即执行数据建模和分析等任务。

在本教程中，您将学习使用基于Power Pivot数据模型的DAX进行数据建模和分析。在本教程中，将使用一个示例利润和分析数据库进行说明。

数据建模与分析概念

您从各种来源获得的数据，称为原始数据，需要进行处理才能用于分析目的。您将在本章——数据建模与分析概念中了解这些内容。

使用Excel Power Pivot进行数据建模与分析

由于您在本教程中将掌握的工具是Excel Power Pivot，因此您需要了解如何在Power Pivot中执行数据建模和分析步骤。您将在本章——使用Excel Power Pivot进行数据建模与分析中更广泛地学习这些内容。

随着您学习后续章节，您将了解Power Pivot、DAX和DAX函数在数据建模和分析中的不同方面。

在本教程结束时，您将能够使用DAX对任何手头环境进行数据建模和分析。

使用DAX进行数据建模 - 概念

商业智能 (BI) 在多个领域和组织中越来越重要。基于历史数据的决策和预测在竞争日益激烈的世界中至关重要。对于任何类型的数据分析，都有大量的内部和外部数据可从不同的来源获取。

然而，挑战在于根据当前需求从可用的海量数据中提取相关数据，并以有利于从数据中预测不同见解的方式存储它。因此，使用关键业务术语获得的数据模型是一种宝贵的沟通工具。数据模型还需要提供一种按需快速生成报告的方法。

用于BI系统的数据建模使您能够应对许多数据挑战。

BI数据模型的先决条件

BI的数据模型应满足进行数据分析的业务需求。以下是任何数据模型都必须满足的最低限度基本要求：

数据模型需要特定于业务

适合一个业务部门的数据模型可能不适合另一个业务部门。因此，必须根据具体的业务、使用的业务术语、数据类型及其关系来开发数据模型。它应该基于组织的目标和决策类型。

数据模型需要内置智能

数据模型应通过元数据、层次结构和继承包含内置智能，以促进高效有效的商业智能流程。通过这种方式，您可以为不同的用户提供一个共同的平台，从而消除流程的重复。

数据模型需要健壮

数据模型应精确地呈现特定于业务的数据。它应该能够有效地进行磁盘和内存存储，以便于快速处理和报告。

数据模型需要可扩展

数据模型应该能够快速有效地适应不断变化的业务场景。可能需要包含新的数据或新的数据类型。可能需要有效地处理数据刷新。

BI数据建模

BI数据建模包括以下步骤：

塑造数据
加载数据
定义表之间的关系
定义数据类型
创建新的数据洞察

塑造数据

构建数据模型所需的数据可能来自各种来源，并且可能采用不同的格式。您需要确定从每个数据源中哪些部分的数据对于特定数据分析是必需的。这称为塑造数据。

例如，如果您要检索组织中所有员工的数据，您需要确定每个员工的哪些详细信息与当前环境相关。换句话说，您需要确定需要导入员工表的哪些列。这是因为，数据模型中表中的列越少，对表的计算速度就越快。

加载数据

您需要加载已识别的数据——包含每个表中所选列的数据表。

定义表之间的关系

接下来，您需要定义各个表之间的逻辑关系，以便于组合这些表中的数据，即，如果您有一个包含产品信息的表——产品——以及一个包含产品的各种销售交易的表——销售——通过定义这两个表之间的关系，您可以按产品汇总销售额。

定义数据类型

为数据模型中的数据标识适当的数据类型对于计算的准确性至关重要。对于您已导入的每个表中的每一列，您都需要定义数据类型。例如，文本数据类型、实数数据类型、整数数据类型等。

创建新的数据洞察

这是BI数据建模中的一个关键步骤。构建的数据模型可能需要与需要了解数据趋势并在很短时间内做出所需决策的几个人共享。因此，从源数据创建新的数据洞察将是有效的，避免了分析的重复工作。

新的数据洞察可以采用特定业务人员可以轻松理解和使用的元数据形式。

数据分析

数据模型准备就绪后，可以根据需要分析数据。呈现分析结果也是一个重要步骤，因为决策将基于报告做出。

使用Excel Power Pivot进行数据建模

Microsoft Excel Power Pivot是进行数据建模和分析的优秀工具。

数据模型是Power Pivot数据库。
DAX是一种公式语言，可用于通过DAX公式创建具有数据模型中数据的数据元数据。
使用数据模型中的数据和元数据创建的Excel Power Pivot 表使您能够分析数据并呈现结果。

在本教程中，您将学习使用Power Pivot数据模型和DAX进行数据建模，以及使用Power Pivot进行数据分析。如果您不熟悉Power Pivot，请参阅Excel Power Pivot教程。

您在上节——数据建模与分析概念中学习了数据建模流程步骤。在本节中，您将学习如何使用Power Pivot数据模型和DAX执行每个步骤。

在以下各节中，您将学习将每个流程步骤应用于Power Pivot数据模型以及如何使用DAX。

塑造数据

在Excel Power Pivot中，您可以从各种类型的数据源导入数据，并且在导入过程中，您可以查看并选择要导入的表和列。

确定数据源。
查找数据源类型。例如，数据库或数据服务或任何其他数据源。
决定在当前环境中哪些数据相关。
决定数据的适当数据类型。在Power Pivot数据模型中，您只能为表中的整列使用一种数据类型。
确定哪些表是事实表，哪些是维度表。
决定表之间的相关逻辑关系。

将数据加载到数据模型中

您可以使用功能区上的Power Pivot窗口中提供的多个选项将数据加载到数据模型中。您可以在“获取外部数据”组中找到这些选项。

您将在本章——将数据加载到数据模型中学习如何将数据从Access数据库加载到数据模型中。

为了说明目的，使用了包含损益数据的Access数据库。

在数据模型中定义数据类型

Power Pivot中数据建模过程的下一步是定义加载到数据模型中的表中列的数据类型。

您将在本章——数据模型中的数据类型定义中学习如何定义表中列的数据类型。

创建表之间的关系

Power Pivot数据建模过程的下一步是在数据模型中创建表之间的关系。

您将在本章——扩展数据模型中学习如何创建表之间的关系。

创建新的数据洞察

在数据模型中，您可以创建创建新的数据洞察所需元数据，方法是：

创建计算列
创建日期表
创建度量值

然后，您可以通过创建基于表中列和在“数据透视表字段”列表中显示为字段的度量值的动态Power Pivot表来分析数据。

添加计算列

表中的计算列是您使用DAX公式添加到表的列。

您将在本章——扩展数据模型中学习如何在数据模型中向表中添加计算列。

创建日期表

要在DAX公式中使用时间智能函数来创建元数据，您需要一个日期表。如果您不熟悉日期表，请参考本章——了解日期表。

您将在本章——扩展数据模型中学习如何在数据模型中创建日期表。

创建度量值

您可以使用DAX函数和DAX公式在数据表中创建各种度量值，以根据当前上下文中数据分析的要求进行不同的计算。

这是使用DAX进行数据建模的关键步骤。

您将在后续章节中学习如何为损益分析的各种目的创建度量值。

使用Power Pivot表分析数据

您可以为损益分析的各个方面创建Power Pivot表。当您在后续章节中学习如何使用DAX创建度量值时，您还将学习如何使用这些度量值和Power Pivot表来分析数据。

将数据加载到数据模型中

您可以将来自不同类型的数据源的数据加载到数据模型中。为此，您可以在Power Pivot窗口功能区的“获取外部数据”组中找到各种选项。

您可以看到，您可以从数据库或数据服务或其他几种类型的数据源加载数据。

当您将数据从数据源加载到数据模型中时，将与数据源建立连接。这使得在源数据更改时可以刷新数据。

从新的数据模型开始

在本节中，您将学习如何为损益分析建模数据。分析数据位于Microsoft Access数据库中。

您可以按如下方式启动新的数据模型：

打开新的Excel工作簿
单击功能区上的“Power Pivot”选项卡
单击“数据模型”组中的“管理”

Power Pivot窗口出现。由于您尚未加载任何数据，因此窗口将为空白。

将数据从Access数据库加载到数据模型

要从Access数据库加载数据，请执行以下步骤：

单击功能区“获取外部数据”组中的“从数据库”。
在下拉列表中单击“从Access”。

出现“表导入向导”对话框。

浏览到Access文件。
为连接提供一个友好的名称。

单击“下一步”按钮。“表导入向导”的下一部分出现。
在“表导入向导”中，选择“从表和视图列表中选择要导入的数据”选项以选择要导入的数据。

单击“下一步”按钮。“表导入向导”的下一部分将如下面的屏幕截图所示出现。
选择所有表。
为表提供友好的名称。这是必要的，因为这些名称会出现在Power Pivot表中，因此应该被每个人理解。

选择表中的列

您可能不需要所选表中的所有列进行当前分析。因此，您只需要选择在数据整形时选择的那些列。

单击“预览和筛选”按钮。“表导入向导”的下一部分——所选表的预览——出现。

如上面的屏幕截图所示，列标题具有复选框。选择要在所选表中导入的列。
单击“确定”。对其他表重复相同的操作。

将数据导入数据模型

您正处于将数据加载到数据模型的最后阶段。单击“表导入向导”中的“完成”按钮。“表导入向导”的下一部分出现。

将显示导入状态。当数据加载完成后，状态最终显示“成功”。

查看数据模型中的数据

导入的表将出现在Power Pivot窗口中。这是数据模型的视图。

您可以观察到以下内容：

每个表都出现在单独的选项卡中。
选项卡名称分别是表名。
数据下方的区域用于计算。

查看连接名称

单击“获取外部数据”组中的“现有连接”。将出现“现有连接”对话框，如下面的屏幕截图所示。

如上面的屏幕截图所示，给定的连接名称显示在“Power Pivot 数据连接”下。

在数据模型中定义数据类型

在Power Pivot数据模型中，列中的所有数据必须具有相同的数据类型。为了进行准确的计算，您需要确保数据模型中每个表中每列的数据类型都符合要求。

数据模型中的表

在前一章创建的数据模型中，共有3个表：

账户
地理位置
财务数据

确保适当的数据类型

要确保表中的列符合要求，您需要在Power Pivot窗口中检查它们的数据类型。

单击表中的一列。
记下功能区“格式”组中显示的列的数据类型。

如果所选列的数据类型不合适，请按如下方式更改数据类型。

单击“格式”组中数据类型旁边的向下箭头。
在下拉列表中单击相应的数据类型。
对数据模型中所有表中的每一列重复此操作。

账户表中的列

在“账户”表中，您有以下列：

序号	列和说明
1	账户每行包含一个账户编号。该列具有唯一值，用于定义与“财务数据”表的关系。
2	类别与每个账户关联的类别。例如 - 费用、净收入等。
3	子类别描述费用或收入的类型。例如 - 人员。

“账户”表中的所有列本质上都是描述性的，因此都是文本数据类型。

地理位置表中的列

“地理位置”表包含有关每个利润中心的数据。

“利润中心”列每行包含一个利润中心标识。此列具有唯一值，用于定义与“财务数据”表的关系。

财务数据表中的列

在“财务数据”表中，您有以下列：

列	说明	数据类型
财政月份	月份和年份	文本
利润中心	利润中心标识	文本
账户	账户编号。每个账户可以有多个利润中心。	文本
预算	每个利润中心的月度预算金额。	货币
实际	每个利润中心的月度实际金额。	货币
预测	每个利润中心的月度预测金额。	货币
实际人员	每个人员账户的每个利润中心的月末实际员工人数。	整数
预算人员	每个人员账户的每个利润中心的月末预算员工人数。	整数
预测人员	每个人员账户的每个利润中心的月末预测员工人数。	整数

数据模型中的表类型

“账户”表和“地理位置”表都是维度表，也称为**查找表**。

**财务数据表**是事实表，也称为数据表。“财务数据”表包含损益计算所需的数据。您还将在此“财务数据”表中以度量值和计算列的形式创建元数据，以便为各种类型的损益计算建模数据，这将随着本教程的进行。

理解数据表

数据分析包括按时间浏览数据以及跨时间段进行计算。例如，您可能需要将今年的利润与去年的利润进行比较。同样，您可能需要预测未来几年的增长和利润。为此，您需要对一段时间进行分组和聚合。

DAX提供多个时间智能函数，可帮助您执行大多数此类计算。但是，这些DAX函数需要一个日期表才能与数据模型中的其他表一起使用。

您可以从数据源导入日期表以及其他数据，也可以在数据模型中自行创建一个日期表。

在本章中，您将了解日期表的不同方面。如果您熟悉Power Pivot数据模型中的日期表，则可以跳过本章并继续后续章节。否则，您可以了解Power Pivot数据模型中的日期表。

什么是日期表？

日期表是数据模型中的一个表，至少包含一列在所需持续时间内连续的日期。它可以包含表示不同时间段的其他列。但是，DAX 时间智能函数需要的是连续日期列。

例如：

日期表可以包含日期、会计月份、会计季度和会计年度等列。
日期表可以包含日期、月份、季度和年份等列。

包含连续日期的日期表

假设您需要在日历年的范围内进行计算。那么，日期表必须至少包含一列连续的日期集，包括该特定日历年中的所有日期。

例如，假设您要浏览的数据的日期范围是从 2014 年 4 月 1 日到 2016 年 11 月 30 日。

如果您必须报告日历年，则需要一个日期表，其中包含一列“日期”，该列按顺序包含从 2014 年 1 月 1 日到 2016 年 12 月 31 日的所有日期。
如果您必须报告会计年度，并且您的会计年度结束日期是 6 月 30 日，则需要一个日期表，其中包含一列“日期”，该列按顺序包含从 2013 年 7 月 1 日到 2017 年 6 月 30 日的所有日期。
如果您必须同时报告日历年和会计年度，则可以使用一个跨越所需日期范围的日期表。

您的日期表必须包含给定持续时间内每一年份的所有日期。这样，您将在该时间段内获得连续的日期。

如果您定期使用新数据刷新数据，则可以将结束日期延长一两年，这样您就不必经常更新日期表。

日期表如下面的屏幕截图所示。（此处应插入屏幕截图）

将日期表添加到数据模型

您可以通过以下任何方式将日期表添加到数据模型：

从关系数据库或任何其他数据源导入。
在 Excel 中创建日期表，然后复制或链接到 Power Pivot 中的新表。
从 Microsoft Azure 市场导入。

在 Excel 中创建日期表并复制到数据模型

在 Excel 中创建日期表并复制到数据模型是创建数据模型中日期表的简便而灵活的方法。

在 Excel 中打开一个新工作表。
在某列的第一行键入“日期”。
在同一列的第二行键入要创建的日期范围的第一个日期。
选择单元格，单击填充柄并将其向下拖动以创建所需日期范围内的连续日期列。

例如，键入 2014/1/1，单击填充柄并向下拖动以填充到 2016/12/31 的连续日期。（此处日期格式根据地区习惯调整）

单击“日期”列。
单击功能区上的“插入”选项卡。
单击“表格”。
验证表格范围。
单击“确定”。

包含单个日期列的表格已在 Excel 中准备好。

选择表格。
单击功能区上的“复制”。
单击 Power Pivot 窗口。
单击功能区上的“粘贴”。

这会将剪贴板的内容添加到数据模型中的新表中。因此，您也可以使用相同的方法在现有数据模型中创建日期表。

粘贴预览对话框将显示，如下面的屏幕截图所示。（此处应插入屏幕截图）

在“表名”框中键入“日期”。
预览数据。

选中“使用第一行作为列标题”复选框。
单击“确定”。

这会将剪贴板的内容复制到数据模型中的新表中。

现在，您在数据模型中拥有一个包含单个连续日期列的日期表。列的标题是“日期”，正如您在 Excel 表格中指定的那样。

向日期表添加新的日期列

接下来，您可以根据计算要求向日期表添加计算列。

例如，您可以添加以下列：日、月、年和季度：

日
=DAY('日期'[日期])
月
=MONTH('日期'[日期])
年
=YEAR('日期'[日期])
季度
=CONCATENATE ("QTR ", INT (('日期'[月份]+2)/3))

数据模型中生成的日期表如下面的屏幕截图所示。（此处应插入屏幕截图）

因此，您可以向日期表添加任意数量的计算列。重要的是，日期表必须包含跨越执行计算的时间段的连续日期列。

为日历年创建日期表

日历年通常包括某年 1 月 1 日到 12 月 31 日的日期，还包括该特定年份的法定节假日。进行计算时，您可能需要仅考虑工作日，不包括周末和节假日。

假设您要为 2017 日历年创建一个日期表。

创建一个包含“日期”列的 Excel 表格，该列包含从 2017 年 1 月 1 日到 2017 年 12 月 31 日的连续日期。（请参考上一节了解如何操作。）
复制 Excel 表格并将其粘贴到数据模型中的新表中。（请参考上一节了解如何操作。）
将表命名为“日历”。
添加以下计算列：
- 日 =DAY('日历'[日期])
- 月 =MONTH('日历'[日期])
- 年 =YEAR('日历'[日期])
- 星期 =FORMAT('日历'[日期],"DDD")
- 月份名称 =FORMAT('日历'[日期],"MMM")

向日历表添加节假日

按如下方式向日历表添加节假日：

获取该年份的法定节假日列表。
例如，对于美国，您可以从以下链接获取任何所需年份的节假日列表：http://www.calendar-365.com/。
将它们复制并粘贴到 Excel 工作表中。
复制 Excel 表格并将其粘贴到数据模型中的新表中。
将表命名为“节假日”。

接下来，您可以使用 DAX LOOKUPVALUE 函数向日历表添加节假日计算列。

=LOOKUPVALUE(节假日[节假日],节假日[日期],'日历'[日期])

DAX LOOKUPVALUE 函数在第二个参数（即 Holidays[Date]）中搜索第三个参数（即 Calendar[Date]），如果匹配，则返回第一个参数（即 Holidays[Holiday]）。结果将类似于以下屏幕截图所示。（此处应插入屏幕截图）

向会计年度添加列

会计年度通常包括从会计年度结束后的第一个月的第一天到下一个会计年度结束日的日期。例如，如果会计年度结束日期是 3 月 31 日，则会计年度范围是从 4 月 1 日到 3 月 31 日。

您可以使用 DAX 公式在日历表中包含会计期间：

添加 FYE 度量值

FYE:=3
添加以下计算列：
- 会计年度
  
  =IF('日历'[月份]<='日历'[FYE],'日历'[年份],'日历'[年份]+1)
- 财政月份
  
  =IF('日历'[月份]<='日历'[FYE],12-'日历'[FYE]+'日历'[月份],'日历'[月份]-'日历'[FYE] )
- 会计季度
  
  =INT(('日历'[会计月份]+2)/3)

设置日期表属性

当您使用 DAX 时间智能函数（例如 TOTALYTD、PREVIOUSMONTH 和 DATESBETWEEN）时，它们需要元数据才能正常工作。日期表属性设置此类元数据。

要设置日期表属性：

在 Power Pivot 窗口中选择日历表。
单击功能区上的“设计”选项卡。
单击“日历”组中的“标记为日期表”。
单击下拉列表中的“标记为日期表”。

出现“标记为日期表”对话框。选择日历表中的“日期”列。这必须是日期类型列，并且必须具有唯一值。单击“确定”。

扩展数据模型

在本节中，您将学习如何扩展前面章节中创建的数据模型。扩展数据模型包括：

添加表
在现有表中添加计算列
在现有表中创建度量值

其中，创建度量值至关重要，因为它涉及在数据模型中提供新的数据洞察，这将使使用数据模型的人员避免返工，并在分析数据和决策时节省时间。

由于损益分析涉及使用时间段，并且您将使用 DAX 时间智能函数，因此您需要数据模型中的日期表。

如果您不熟悉日期表，请阅读“了解日期表”一章。

您可以按如下方式扩展数据模型：

要创建数据表（即财务数据表）和日期表之间的关系，您需要在财务数据表中创建一个计算列“日期”。
要执行不同类型的计算，您需要在数据表（财务数据）和查找表（帐户和地理位置）之间创建关系。
您需要创建各种度量值，以帮助您执行多项计算并进行所需的分析。

这些步骤实质上构成了使用数据模型进行损益分析的数据建模步骤。但是，这是您要使用 Power Pivot 数据模型执行任何类型的数据分析的步骤顺序。

此外，您将在后续章节中学习如何创建度量值以及如何在 Power Pivot 表中使用它们。这将使您充分了解使用 DAX 进行数据建模以及使用 Power Pivot 表进行数据分析。

将日期表添加到数据模型

为跨越会计年度的时间段创建日期表，方法如下：

在新的 Excel 工作表中创建一个表，该表包含单个列，列标题为“日期”，并且包含从 2011/7/1 到 2018/6/30 的连续日期。（此处日期格式根据地区习惯调整）
从 Excel 复制该表并将其粘贴到 Power Pivot 窗口中。这将在 Power Pivot 数据模型中创建一个新表。
将表命名为“日期”。
确保日期表中的“日期”列的数据类型为日期 (DateTime)。

接下来，您需要按如下方式向日期表添加计算列：会计年度、会计季度、会计月份和月份：

会计年度

假设会计年度结束日期是 6 月 30 日。那么，会计年度范围是从 7 月 1 日到 6 月 30 日。例如，2011 年 7 月 1 日 (2011/7/1) 到 2012 年 6 月 30 日 (2012/6/30) 将是 2012 会计年度。（此处日期格式根据地区习惯调整）

在日期表中，假设您想将其表示为 FY2012。

您需要首先提取日期的会计年度部分，并将其与 FY 附加。
- 对于 2011 年 7 月到 2011 年 12 月的日期，会计年度为 1+2011。
- 对于2012年1月到2012年6月期间的日期，财年为0+2012。
- 一般来说，如果财务年度结束月份为FYE，则执行以下操作：
  
  ((月份 – 1)/FYE)的整数部分 + 年份
- 接下来，取最右边的4个字符以获得财务年度。
在DAX中，您可以表示为：

RIGHT(INT((MONTH('Date'[Date])-1)/'Date'[FYE])+YEAR('Date'[Date]),4)
在日期表中添加计算列“财政年度”，使用DAX公式：

="FY"&RIGHT(INT((MONTH('Date'[Date])-1)/'Date'[FYE])+YEAR('Date'[Date]),4)

会计季度

如果FYE代表财务年度结束月份，则财务季度计算如下：

((（月份+FYE-1）/12的余数) + 3)/3 的整数部分

在DAX中，您可以表示为：

INT((MOD(MONTH('Date'[Date])+'Date'[FYE]-1,12)+3)/3)
在日期表中添加计算列“财政季度”，使用DAX公式：

='Date'[FiscalYear]&"-Q"&FORMAT( INT((MOD(MONTH('Date'[Date]) + 'Date'[FYE]-1,12) + 3)/3), "0")

财政月份

如果FYE代表财务年度结束月份，则财务月度计算如下：

(（月份+FYE-1）/12的余数) + 1

在DAX中，您可以表示为：

MOD(MONTH('Date'[Date])+'Date'[FYE]-1,12)+1
在日期表中添加计算列“财政月份”，使用DAX公式：

='Date'[Fiscal Year]&"-P" & FORMAT(MOD(MONTH([Date])+[FYE]-1,12)+1,"00")

月

最后，添加计算列“月份”，表示财务年度中的月份编号，如下所示：

=FORMAT(MOD(MONTH([Date])+[FYE]-1,12)+1,"00") & "-" & FORMAT([Date],"mmm")

生成的日期表如下截图所示。

将表 – 日期标记为日期表，并将列 – 日期作为具有唯一值的列，如下面的截图所示。

添加计算列

要在财务数据表和日期表之间创建关系，需要在财务数据表中有一列日期值。

在财务数据表中添加计算列“日期”，使用DAX公式：

= DATEVALUE ('Finance Data'[Fiscal Month])

定义数据模型中表之间的关系

数据模型中包含以下表：

数据表 - 财务数据
查找表 - 账户和地理位置
日期表 - 日期

要定义数据模型中表之间的关系，请执行以下步骤：

在Power Pivot的图表视图中查看表。
在表之间创建以下关系：
- 财务数据表和账户表之间使用列“账户”的关系。
- 财务数据表和地理位置表之间使用列“利润中心”的关系。
- 财务数据表和日期表之间使用列“日期”的关系。

隐藏客户端工具中的列

如果数据表中存在任何不会用作任何数据透视表中字段的列，则可以在数据模型中隐藏它们。然后，它们在数据透视表字段列表中不可见。

在财务数据表中，有4列 – 财政月份、日期、账户和利润中心，您不会将它们用作任何数据透视表中的字段。因此，您可以隐藏它们，以使它们不会出现在数据透视表字段列表中。

在财务数据表中选择列 – 财政月份、日期、账户和利润中心。
右键单击并选择下拉列表中的“隐藏客户端工具”。

在表中创建度量值

您可以使用数据模型和Power PivotTable进行数据建模和分析。

在后续章节中，您将学习如何创建度量值以及如何在Power PivotTable中使用它们。您将在数据表（即财务数据表）中创建所有度量值。

您将在数据表 – 财务数据中使用DAX公式创建度量值，您可以将这些度量值用于任意数量的数据透视表进行数据分析。度量值本质上是元数据。在数据表中创建度量值是数据建模的一部分，在Power PivotTable中汇总它们是数据分析的一部分。

基本财务度量值和分析

您可以在数据模型中创建各种度量值，以便在任意数量的Power PivotTable中使用。这构成了使用DAX进行数据建模和分析数据模型的过程。

正如您在前面几节中所学到的，数据建模和分析取决于具体的业务和上下文。在本节中，您将学习基于示例损益数据库的数据建模和分析，以了解如何创建所需的度量值并在各种Power PivotTable中使用它们。

您可以将相同的方法应用于任何业务和上下文的数据建模和分析。

基于财务数据创建度量值

要创建任何财务报表，您需要对特定时期、组织、账户或地理位置的金额进行计算。您还需要执行员工人数和每位员工的成本计算。在数据模型中，您可以创建可在创建其他度量值时重复使用的基本度量值。这是使用DAX进行数据建模的有效方法。

为了执行损益数据分析的计算，您可以创建诸如总和、同比、年初至今、本季度至今、差异、员工人数、每位员工的成本等的度量值。您可以将这些度量值用于Power PivotTable来分析数据并报告分析结果。

在以下各节中，您将学习如何创建基本财务度量值以及如何使用这些度量值分析数据。这些度量值被称为基本度量值，因为它们可用于创建其他财务度量值。您还将学习如何为之前的时期创建度量值并在分析中使用它们。

创建基本财务度量值

在财务数据分析中，预算和预测起着重要作用。

预算

预算是对公司财务年度收入和支出的估计。预算是在财务年度开始时计算的，同时考虑到公司的目标和指标。在财务年度期间，需要定期分析预算度量值，因为市场状况可能会发生变化，公司可能需要将其目标和指标与行业当前趋势相协调。

预测

财务预测是通过检查公司历史收入和支出数据来估计公司未来的财务结果。您可以将财务预测用于以下目的：

确定如何分配未来期间的预算。
跟踪公司的预期业绩。
及时做出决策，以解决与目标相比的不足，或最大限度地利用新兴机遇。

实际值

要执行预算和预测计算，您需要随时了解实际收入和支出。

您可以创建以下3个基本财务度量值，这些度量值可用于在数据模式中创建其他财务度量值：

预算总和
实际总和
预测总和

这些度量值是对财务数据表中列 – 预算、实际和预测的聚合总和。

创建基本财务度量值如下：

预算总和

预算总和:=SUM('Finance Data'[Budget])

实际总和

实际总和:=SUM('Finance Data'[Actual])

预测总和

预测总和:=SUM('Finance Data'[Forecast])

使用基本财务度量值分析数据

使用基本财务度量值和日期表，您可以执行以下分析：

创建一个Power PivotTable。
将日期表中的字段“财政年度”添加到行。
将度量值“预算总和”、“实际总和”和“预测总和”（显示为数据透视表字段列表中的字段）添加到值。

创建先前期间的财务度量值

使用三个基本财务度量值和日期表，您可以创建其他财务度量值。

假设您想将一个季度的实际总和与上一个季度的实际总和进行比较。您可以创建度量值 – 上季度实际总和。

上季度实际总和:=CALCULATE([Actual Sum], DATEADD('Date'[Date],1,QUARTER))

同样，您可以创建度量值 – 上一年实际总和。

上一年实际总和:=CALCULATE([Actual Sum], DATEADD('Date'[Date],1,YEAR))

使用先前期间的财务度量值分析数据

使用基本度量值、先前期间的度量值和日期表，您可以执行以下分析：

创建一个Power PivotTable。
将日期表中的字段“财政季度”添加到行。
将度量值“实际总和”和“上季度实际总和”添加到值。
创建另一个Power PivotTable。
将日期表中的字段“财政年度”添加到行。
将度量值“实际总和”和“上一年实际总和”添加到值。

同比财务度量值和分析

同比 (YoY) 是衡量增长的指标。它是通过从实际总和中减去上一年实际总和获得的。

如果结果为正，则表示实际值增加；如果结果为负，则表示实际值减少，即如果我们将同比计算为：

同比 = (实际总和 – 上一年实际总和)

如果实际总和 > 上一年实际总和，则同比将为正。
如果实际总和 < 上一年实际总和，则同比将为负。

在财务数据中，诸如支出账户之类的账户将具有借方（正）金额，而收入账户将具有贷方（负）金额。因此，对于支出账户，上述公式效果很好。

但是，对于收入账户，它应该是相反的，即

如果实际总和 > 上一年实际总和，则同比应为负。
如果实际总和 < 上一年实际总和，则同比应为正。

因此，对于收入账户，您必须将同比计算为：

同比 = -(实际总和 – 上一年实际总和)

创建同比度量值

您可以使用以下DAX公式创建同比度量值：

同比:=IF(CONTAINS(Accounts, Accounts[Class],"净收入"),-([Actual Sum]-[Prior Year Actual Sum]), [Actual Sum]-[Prior Year Actual Sum])

在上述DAX公式中：

DAX CONTAINS 函数返回 TRUE，如果某行在账户表中的 Class 列中包含“净收入”。
然后，DAX IF 函数返回 –([Actual Sum]-[ Prior Year Actual Sum])。
否则，DAX IF 函数返回 [Actual Sum]-[ Prior Year Actual Sum]。

创建同比百分比度量值

您可以使用以下比率表示同比百分比：

(同比) / (上一年实际总和)

您可以使用以下DAX公式创建同比百分比度量值：

同比 %:=IF([Prior Year Actual Sum], [YoY] / ABS([Prior Year Actual Sum]),BLANK())

在上述公式中使用DAX IF 函数，以确保不会出现被零除的情况。

使用同比度量值分析数据

按如下方式创建Power PivotTable：

将账户表中的字段“类别”和“子类别”添加到行。
将度量值 – 实际总和、上一年实际总和、同比和同比 % 添加到值。
在日期表中的字段“财政年度”上插入切片器。
在切片器中选择 FY2016。

创建预算同比度量值

您可以按如下方式创建预算同比度量值：

预算同比: = IF(CONTAINS(Accounts,Accounts[Class],"净收入"), - ([Budget Sum] - [Prior Year Actual Sum]), [Budget Sum] - [Prior Year Actual Sum])

创建预算同比百分比度量值

您可以按如下方式创建预算同比百分比度量值：

预算同比 %:=IF([Prior Year Actual Sum] , [Budget YoY] / ABS ([Prior Year Actual Sum]) , BLANK())

使用预算同比度量值分析数据

按如下方式创建Power PivotTable：

将账户表中的字段“类别”和“子类别”添加到行。
将度量值 – 预算总和、上一年实际总和、预算同比和预算同比 % 添加到值。
在日期表中的字段“财政年度”上插入切片器。
在切片器中选择 FY2016。

创建预测同比度量值

您可以按如下方式创建预测同比度量值：

预测同比:=IF(CONTAINS(Accounts,Accounts[Class],"净收入"), - ([Forecast Sum] - [Prior Year Actual Sum]), [Forecast Sum] - [Prior Year Actual Sum])

创建预测同比百分比度量值

您可以按如下方式创建预测同比百分比度量值：

预测同比 %:=IF([Prior Year Actual Sum],[Forecast YoY]/ABS([Prior Year Actual Sum]),BLANK())

使用预测同比度量值分析数据

按如下方式创建Power PivotTable：

将账户表中的字段“类别”和“子类别”添加到行。
将预测总和、上一年实际总和、同比预测和同比预测百分比添加到“值”中。
在数据表中的“财政年度”字段上插入切片器。
在切片器中选择 FY2016。

差异度量和分析

您可以创建诸如与预算的差异、与预测的差异以及预测与预算的差异等差异度量。您还可以根据这些度量分析财务数据。

创建与预算差异总和度量

按如下方式创建与预算差异总和度量 (VTB Sum)：−

VTB Sum:=[预算总和]-[实际总和]

创建与预算差异百分比度量

按如下方式创建与预算差异百分比度量 (VTB %)：−

VTB %:=IF([预算总和],[VTB Sum]/ABS([预算总和]),BLANK())

使用与预算差异度量分析数据

按如下方式创建Power PivotTable：

将“日期”表中的“财政年度”添加到“行”中。
将“财务数据”表中的“实际总和”、“预算总和”、“VTB Sum”、“VTB %”度量添加到“值”中。

创建与预测差异总和度量

按如下方式创建与预测差异总和 (VTF Sum) 度量：−

VTF Sum:=[预测总和]-[实际总和]

创建与预测差异百分比度量

按如下方式创建与预测差异百分比度量 (VTF %)：−

VTF %:=IF([预测总和],[VTF Sum]/ABS([预测总和]),BLANK())

使用与预测差异度量分析数据

按如下方式创建Power PivotTable：

将“日期”表中的“财政年度”添加到“行”中。
将“财务数据”表中的“实际总和”、“预测总和”、“VTF Sum”、“VTF %”度量添加到“值”中。

创建预测与预算差异总和度量

按如下方式创建预测与预算差异总和 (Forecast VTB Sum) 度量：−

Forecast VTB Sum:=[预算总和]-[预测总和]

创建预测与预算差异百分比度量

按如下方式创建预测与预算差异百分比 (Forecast VTB Percentage) 度量：−

Forecast VTB %:=IF([预算总和],[Forecast VTB Sum]/ABS([预算总和]),BLANK())

使用预测与预算差异度量分析数据

按如下方式创建Power PivotTable：

将“日期”表中的“财政年度”添加到“行”中。
将“财务数据”表中的“预算总和”、“预测总和”、“Forecast VTB Sum”、“Forecast VTB %”度量添加到“值”中。

年初至今度量和分析

要计算包含从某个时期（例如财政年度）开始的期初余额到特定时间段的结果，可以使用 DAX 时间智能函数。这将使您可以按月分析数据。

在本节中，您将学习如何创建年初至今度量以及如何使用相同的度量进行数据分析。

创建年初至今实际总和度量

按如下方式创建年初至今实际总和度量：−

YTD Actual Sum:=TOTALYTD([实际总和], 'Date'[Date], ALL('Date'), "6/30")

创建年初至今预算总和度量

按如下方式创建年初至今预算总和度量：−

YTD Budget Sum:=TOTALYTD([预算总和], 'Date'[Date], ALL('Date'), "6/30")

创建年初至今预测总和度量

按如下方式创建年初至今预测总和度量：−

YTD Forecast Sum:=TOTALYTD([预测总和], 'Date'[Date], ALL('Date'), "6/30")

创建上一年年初至今实际总和度量

按如下方式创建上一年年初至今实际总和度量：−

Prior YTD Actual Sum:=TOTALYTD([上一年实际总和], 'Date'[Date], ALL('Date'), "6/30")

使用年初至今度量分析数据

按如下方式创建Power PivotTable：

将“日期”表中的“月份”添加到“行”中。
将“财务数据”表中的“实际总和”、“年初至今实际总和”、“年初至今预算总和”和“年初至今预测总和”度量添加到“值”中。
在“日期”表中的“财政年度”上插入切片器。
在切片器中选择 FY2016。

按如下方式创建Power PivotTable：

将“日期”表中的“月份”添加到“行”中。
将“财务数据”表中的“实际总和”、“年初至今实际总和”、“上一年实际总和”和“上一年年初至今实际总和”度量添加到“值”中。
在“日期”表中的“财政年度”上插入切片器。
在切片器中选择 FY2016。

本季度至今度量和分析

要计算包含从某个时期（例如财政季度）开始的期初余额到特定时间段的结果，可以使用 DAX 时间智能函数。这将使您可以按月分析数据。

在本节中，您将学习如何创建本季度至今度量以及如何使用相同的度量进行数据分析。

创建本季度至今总和度量

按如下方式创建本季度至今实际总和度量：−

QTD Actual Sum:=TOTALQTD([实际总和],'Date'[Date],ALL('Date'))

创建本季度至今预算总和度量

按如下方式创建本季度至今预算总和度量：−

QTD Budget Sum:=TOTALQTD([预算总和], 'Date'[Date], ALL('Date'))

创建本季度至今预测总和度量

按如下方式创建本季度至今预算总和度量：−

QTD Budget Sum:=TOTALQTD([预算总和], 'Date'[Date], ALL('Date'))

创建本季度至今预测总和度量

按如下方式创建本季度至今预测总和度量：−

QTD Forecast Sum:=TOTALQTD([预测总和], 'Date'[Date], ALL('Date'))

创建上一个季度至今实际总和度量

按如下方式创建上一个季度至今实际总和度量：−

Prior QTD Actual Sum:=TOTALQTD([上个季度实际总和], 'Date'[Date], ALL('Date'))

使用本季度至今度量分析数据

按如下方式创建Power PivotTable：

将“日期”表中的“财政月份”添加到“行”中。
将“财务数据”表中的“实际总和”、“本季度至今实际总和”、“本季度至今预算总和”和“本季度至今预测总和”度量添加到“值”中。
在“日期”表中的“财政季度”上插入切片器。
在切片器中选择 FY2016-Q2。

按如下方式创建Power PivotTable：

将“日期”表中的“财政月份”添加到“行”中。
将“财务数据”表中的“实际总和”、“本季度至今实际总和”、“上个季度实际总和”和“上个季度至今实际总和”度量添加到“值”中。
在“日期”表中的“财政季度”上插入切片器。
在切片器中选择 FY2016-Q1。

预算度量和分析

预算编制包括估算公司在一个财政年度内的现金流。在预算编制中，会考虑公司的财务状况、目标、预期收入和支出。

但是，市场状况可能会在财政年度内发生变化，公司可能需要重置其目标。这需要使用在财政年度开始时估算的预算（预算总和）和从财政年度开始至今实际支出的总和（年初至今实际总和）来分析财务数据。

在财政年度的任何时间，您可以计算以下内容：−

未支出余额

未支出余额是在实际支出后剩余的预算，即

未支出余额 = 年初至今预算总和 – 年初至今实际总和

预算达成率 %

预算达成率 % 是您至今为止已花费的预算百分比，即

预算达成率 % = 年初至今实际总和 / 年初至今预算总和

这些计算有助于使用预算进行决策的公司。

创建未支出余额度量

您可以按如下方式创建未支出余额度量：−

未支出余额:=CALCULATE( [YTD Budget Sum],ALL('Finance Data'[Date]) )-[YTD Actual Sum]

创建预算达成率百分比度量

您可以按如下方式创建预算达成率百分比度量：−

预算达成率 %:=IF([YTD Budget Sum],[YTD Actual Sum]/CALCULATE([YTD Budget Sum],ALL('Finance Data'[Date])),BLANK())

使用预算度量分析数据

按如下方式创建Power PivotTable：

将“日期”表中的“月份”添加到“行”中。
将“财务数据”表中的“预算总和”、“年初至今预算总和”、“年初至今实际总和”、“预算达成率 %”和“未支出余额”度量添加到“值”中。
在“财政年度”字段上插入切片器。
在切片器中选择 FY2016。

预测度量和分析

您可以使用预测度量来分析财务数据，并帮助组织对其年度目标和指标进行必要的调整，以使公司的业绩与不断变化的业务需求相符。

您需要定期更新预测以适应变化。然后，您可以将最新的预测与财政年度剩余期间的预算进行比较，以便公司可以进行必要的调整以满足业务变化。

在财政年度的任何时间，您可以计算以下内容：−

预测达成率 %

预测达成率 % 是您至今为止已花费的预测总和的百分比，即

预测达成率 % = 年初至今实际总和 / 年初至今预测总和

预测未支出余额

预测未支出余额是在实际支出后剩余的预测总和，即

预测未支出余额 = 年初至今预测总和 – 年初至今实际总和

预算调整

预算调整是组织根据预测需要进行的预算总和调整（增加或减少）。

预算调整 = 预测未支出余额 - 未支出余额

如果结果值为正，则需要增加预算。否则，可以将其调整用于其他目的。

创建预测达成率百分比度量

您可以按如下方式创建预测达成率百分比度量：−

预测达成率百分比:= IF([YTD Forecast Sum], [YTD Actual Sum]/[YTD Forecast Sum], BLANK())

创建预测未支出余额度量

您可以按如下方式创建预测未支出余额度量：−

预测未支出余额:=[YTD Forecast Sum]-[YTD Actual Sum]

创建预算调整度量

您可以按如下方式创建预算调整度量：−

预算调整:=[预测未支出余额]-[未支出余额]

使用预测度量分析数据

按如下方式创建Power PivotTable：

将“日期”表中的“月份”添加到“行”中。
将“财务数据”表中的“预算总和”、“年初至今预算总和”、“年初至今实际总和”、“预算达成率 %”和“未支出余额”度量添加到“值”中。
在“财政年度”上插入切片器。
在切片器中选择 FY2016。

月份计数指标

您可以创建“月份数量”度量，这些度量可用于创建“员工数量”度量和“人均成本”度量。“财务数据”表中“实际”列/“预算”列/“预测”列具有非零值的“财政月份”列的这些度量计数不同的值。这是必需的，因为“财务数据”表在“实际”列中包含零值，并且在计算“员工数量”和“人均成本”时应排除这些行。

创建“实际月份数量”度量

您可以按如下方式创建“实际月份数量”度量：−

CountOfActualMonths:=CALCULATE(DISTINCTCOUNT('FinanceData' [Fiscal Month]),'Finance Data'[Actual]<>0)

创建“预算月份数量”度量

您可以按如下方式创建“预算月份数量”度量：−

CountOfBudgetMonths:=CALCULATE(DISTINCTCOUNT('FinanceData' [Fiscal Month]),'Finance Data'[Budget]<>0)

创建“预测月份数量”度量

您可以按如下方式创建“预测月份数量”度量：−

CountOfForecastMonths:=CALCULATE(DISTINCTCOUNT('FinanceData' [Fiscal Month]),'Finance Data'[Forecast]<>0)

期末员工人数指标

您可以为特定时间段创建“期末员工数量”度量。“期末员工数量”是在指定期间的最后日期（我们有非空人员总和）的人员总和。

期末员工数量的获取方式如下：−

对于月份：特定月份结束时的员工总和。
对于季度：特定季度最后一个月份结束时的员工总和。
对于年份：特定年份最后一个月份结束时的员工总和。

创建“实际期末员工数量”度量

您可以按如下方式创建“实际期末员工数量”度量：−

Actual Ending Head Count:=CALCULATE(SUM('Finance Data'[Actual People]),LASTNONBLANK('Finance Data'[Date], IF(CALCULATE(SUM('Finance Data'[Actual People]), ALL(Accounts))=0, BLANK(), CALCULATE(SUM('Finance Data'[Actual People]), ALL(Accounts)))), ALL(Accounts))

如上所述使用的 DAX LASTNONBLANK 函数返回我们有非空人员总和的最后日期，以便您可以计算该日期的人员总和。

创建“预算期末员工数量”度量

您可以按如下方式创建“预算期末员工数量”度量：−

Budget Ending Head Count: = CALCULATE(SUM('Finance Data'[Budget People]),LASTNONBLANK('Finance Data'[Date], IF(CALCULATE(SUM('Finance Data'[Budget People]), ALL(Accounts))=0, BLANK(), CALCULATE(SUM('Finance Data'[Budget People]), ALL(Accounts)))), ALL(Accounts))

创建“预测期末员工数量”度量

您可以按如下方式创建“预测期末员工数量”度量：−

Forecast Ending Head Count:= CALCULATE(SUM('Finance Data'[Forecast People]), LASTNONBLANK('Finance Data'[Date], IF(CALCULATE(SUM('Finance Data'[Forecast People]), ALL(Accounts))=0, BLANK(),CALCULATE(SUM('Finance Data'[Forecast People]), ALL(Accounts)))), ALL(Accounts))

创建上一年实际期末员工数量度量

您可以按如下方式创建上一年实际期末员工数量度量：−

Prior Year Actual Ending Headcount:=CALCULATE('Finance Data'[Actual Ending Head Count], DATEADD('Date'[Date],-1,YEAR))

使用期末员工数量度量分析数据

按如下方式创建Power PivotTable：

将“日期”表中的“财政年度”和“月份”字段添加到“行”中。
将“实际期末员工人数”、“预算期末员工人数”、“预测期末员工人数”、“上一年实际期末员工人数”这四个度量值从“财务数据”表添加到“值”区域。
在“财政年度”字段上插入切片器。
在切片器中选择 FY2016。

平均员工人数指标

在上一章节中，您学习了如何计算特定期间的期末员工人数。同样，您可以为任何给定的月份选择创建平均月员工人数。

平均月员工人数是每月员工人数之和除以所选月份数。

您可以使用 DAX AVERAGEX 函数创建这些度量值。

创建实际平均员工人数度量值

您可以按如下方式创建实际平均员工人数度量值：

实际平均员工人数:=AVERAGEX(VALUES('财务数据'[会计月份]), [实际期末员工人数])

创建预算平均员工人数度量值

您可以按如下方式创建实际平均员工人数度量值：

预算平均员工人数:=AVERAGEX(VALUES('财务数据'[会计月份]), [预算期末员工人数])

创建预测平均员工人数度量值

您可以按如下方式创建预测平均员工人数度量值：

预测平均员工人数:=AVERAGEX(VALUES('财务数据'[会计月份]), [实际期末员工人数])

创建上一年实际平均员工人数度量值

您可以按如下方式创建上一年实际平均员工人数度量值：

上一年实际平均员工人数:=CALCULATE('财务数据'[实际平均员工人数], DATEADD('日期'[日期], -1, YEAR))

使用平均员工人数度量值分析数据

按如下方式创建Power PivotTable：

将“日期”表中的“财政年度”和“月份”字段添加到“行”中。
将“实际平均员工人数”、“预算平均员工人数”、“预测平均员工人数”、“上一年实际平均员工人数”这四个度量值从“财务数据”表添加到“值”区域。
在“财政年度”字段上插入切片器。
在切片器中选择 FY2016。

总员工人数指标

在前面的章节中，您学习了如何创建月份数度量值和平均员工人数度量值。您可以使用这些度量值来计算基础员工人数度量值：

实际总员工人数
预算总员工人数
预测总员工人数

在后续章节中，您将学习如何在其他计算（如同比员工人数和差异度量值）中使用这些基础员工人数度量值。

创建实际总员工人数度量值

您可以按如下方式创建实际总员工人数度量值：

实际总员工人数:= '财务数据'[实际平均员工人数]*'财务数据'[实际月份数]

创建预算总员工人数度量值

您可以按如下方式创建预算总员工人数度量值：

预算总员工人数:= '财务数据'[预算平均员工人数]*'财务数据'[预算月份数]

创建预测总员工人数度量值

您可以按如下方式创建预测总员工人数度量值：

预测总员工人数:= '财务数据'[预测平均员工人数]*'财务数据'[预测月份数]

同比员工人数度量值和分析

在上一章节中，您学习了如何创建基础员工人数度量值，即实际总员工人数、预算总员工人数和预测总员工人数。

在本节中，您将学习如何创建同比员工人数度量值以及如何使用这些度量值分析数据。

创建同比实际期末员工人数度量值

您可以按如下方式创建同比实际期末员工人数度量值：

同比实际期末员工人数:=[实际期末员工人数]-[上一年实际期末员工人数]

创建同比实际平均员工人数度量值

您可以按如下方式创建同比实际平均员工人数度量值：

同比实际平均员工人数:= [实际平均员工人数]-[上一年实际平均员工人数]

创建同比实际总员工人数度量值

您可以按如下方式创建同比实际总员工人数度量值：

同比实际总员工人数:=[实际总员工人数]-[上一年实际总员工人数]

使用同比实际员工人数度量值分析数据

按如下方式创建Power PivotTable：

将“会计季度”和“月份”字段从“日期”表添加到“行”区域。
将“实际期末员工人数”、“上一年实际期末员工人数”、“同比实际期末员工人数”这三个度量值添加到“值”区域。
在“会计年度”字段上插入一个切片器。
在切片器中选择 FY2016。

在同一工作表上创建另一个 Power Pivot 表，步骤如下：

将“会计季度”和“月份”字段从“日期”表添加到“行”区域。
将“实际平均员工人数”、“上一年实际平均员工人数”、“同比实际平均员工人数”这三个度量值添加到“值”区域。

按如下方式将切片器连接到此数据透视表：

单击切片器。
单击功能区上“切片器工具”下的“选项”选项卡。
单击“报表连接”。

将出现“报表连接”对话框。

选择以上两个数据透视表。
单击“确定”。

创建同比预算期末员工人数度量值

您可以按如下方式创建同比预算期末员工人数度量值：

同比预算期末员工人数:= [预算期末员工人数]-[上一年实际期末员工人数]

创建同比预算平均员工人数度量值

您可以按如下方式创建同比预算平均员工人数度量值：

同比预算平均员工人数:= [预算平均员工人数]-[上一年实际平均员工人数]

创建同比预算总员工人数度量值

您可以按如下方式创建同比预算总员工人数度量值：

同比预算总员工人数:=[预算总员工人数]-[上一年实际总员工人数]

创建同比预测期末员工人数度量值

您可以按如下方式创建同比预测期末员工人数度量值：

同比预测期末员工人数:= [预测期末员工人数]-[上一年实际期末员工人数]

创建同比预测平均员工人数度量值

您可以按如下方式创建同比预测平均员工人数度量值：

同比预测平均员工人数:= [预测平均员工人数]-[上一年实际平均员工人数]

创建同比预测总员工人数度量值

您可以按如下方式创建同比预测总员工人数度量值：

同比预测总员工人数:=[预测总员工人数]-[上一年实际总员工人数]

员工人数差异指标

您可以根据到目前为止创建的员工人数度量值创建差异员工人数度量值。

创建与预算期末员工人数的差异度量值

您可以按如下方式创建与预算期末员工人数的差异度量值：

与预算期末员工人数的差异:= '财务数据'[预算期末员工人数]-'财务数据'[实际期末员工人数]

创建与预算平均员工人数的差异度量值

您可以按如下方式创建与预算平均员工人数的差异度量值：

与预算平均员工人数的差异:= '财务数据'[预算平均员工人数]-'财务数据'[实际平均员工人数]

创建与预算总员工人数的差异度量值

您可以按如下方式创建与预算总员工人数的差异度量值：

与预算总员工人数的差异:= '财务数据'[预算总员工人数]-'财务数据'[实际总员工人数]

创建与预测期末员工人数的差异度量值

您可以按如下方式创建与预测期末员工人数的差异度量值：

与预测期末员工人数的差异:= '财务数据'[预测期末员工人数]-'财务数据'[实际期末员工人数]

创建与预测平均员工人数的差异度量值

您可以按如下方式创建与预测平均员工人数的差异度量值：

与预测平均员工人数的差异:= '财务数据'[预测平均员工人数]-'财务数据'[实际平均员工人数]

创建与预测总员工人数的差异度量值

您可以按如下方式创建与预测总员工人数的差异度量值：

与预测总员工人数的差异:= '财务数据'[预测总员工人数]-'财务数据'[实际总员工人数]

创建预测与预算期末员工人数的差异度量值

您可以按如下方式创建预测与预算期末员工人数的差异度量值：

预测与预算期末员工人数的差异:= '财务数据'[预算期末员工人数]-'财务数据'[预测期末员工人数]

创建预测与预算平均员工人数的差异度量值

您可以按如下方式创建预测与预算平均员工人数的差异度量值：

预测与预算平均员工人数的差异:='财务数据'[预算平均员工人数]-'财务数据'[预测平均员工人数]

创建预测与预算总员工人数的差异度量值

您可以按如下方式创建预测与预算总员工人数的差异度量值：

预测与预算总员工人数的差异:= '财务数据'[预算总员工人数]-'财务数据'[预测总员工人数]

人均成本度量值和分析

您已经学习了两种主要的度量值类别：

财务度量值。
员工人数度量值。

您将学习的第三个主要度量值类别是人员成本度量值。任何组织都想知道年度人均成本。年度人均成本代表公司拥有一个员工一整年的成本。

要创建人均成本度量值，您需要首先创建某些初步的人员成本度量值。在“账户”表中，您有一个名为“子类别”的列，其中包含“人员”作为其中一个值。因此，您可以对“账户”表上的“子类别”列应用筛选器，以获得对“财务数据”表的筛选器上下文，从而获得人员成本。

您可以使用获得的人员成本度量值和月份数度量值来创建年度人员成本度量值。最后，您可以从年度人员成本度量值和平均员工人数度量值创建年度人均成本度量值。

创建实际人员成本度量值

您可以按如下方式创建实际人员成本度量值：

实际人员成本:=CALCULATE('财务数据'[实际总额], FILTER('财务数据', RELATED(账户[子类别])="人员"))

创建预算人员成本度量值

您可以按如下方式创建预算人员成本度量值：

预算人员成本:=CALCULATE('财务数据'[预算总额], FILTER('财务数据', RELATED(账户[子类别])="人员"))

创建预测人员成本度量值

您可以按如下方式创建预测人员成本度量值：

预测人员成本:=CALCULATE('财务数据'[预测总额], FILTER('财务数据', RELATED(账户[子类别])="人员"))

创建年度化实际人员成本度量值

您可以按如下方式创建年度化实际人员成本度量值：

年度化实际人员成本:=IF([实际月份数],[实际人员成本]*12/[实际月份数],BLANK())

创建年度化预算人员成本度量值

您可以按如下方式创建年度化预算人员成本度量值：

年度化预算人员成本:=IF([预算月份数], [预算人员成本]*12/[预算月份数],BLANK())

创建年度化预测人员成本度量值

您可以按如下方式创建年度化预测人员成本度量值：

年度化预测人员成本:=IF([预测月份数],[预测人员成本]*12/[预测月份数],BLANK())

创建实际年度化人均成本度量值

您可以按照以下步骤创建实际年度每人成本 (CPH) 度量：

实际年度CPH:=IF([实际平均人数], [年度实际人员成本]/[实际平均人数],BLANK() )

创建预算年度每人成本度量

您可以按照以下步骤创建预算年度每人成本 (CPH) 度量：

预算年度CPH:=IF([预算平均人数],[年度预算人员成本]/[预算平均人数],BLANK())

创建预测年度每人成本度量

您可以按照以下步骤创建预测年度每人成本 (CPH) 度量：

预测年度CPH:=IF([预测平均人数],[年度预测人员成本]/[预测平均人数], BLANK())

创建上一年实际年度每人成本度量

您可以按照以下步骤创建上一年实际年度每人成本 (CPH) 度量：

上一年实际年度CPH:=CALCULATE([实际年度CPH], DATEADD('Date'[Date],-1,YEAR) )

使用每人成本度量分析数据

按如下方式创建Power PivotTable：

将“日期”表中的“财务季度”和“财务月份”字段添加到“行”。
将“实际年度CPH”、“预算年度CPH”和“预测年度CPH”度量添加到“列”。
将“日期”表中的“财务年度”字段添加到“筛选器”。
在筛选器中选择FY2016。

按照以下步骤创建另一个Power PivotTable：

将“日期”表中的“财务季度”字段添加到“行”。
将“实际年度CPH”和“上一年实际年度CPH”度量添加到“列”。
在“日期”表中的“财务年度”字段上插入一个切片器。
在切片器中选择FY2015和FY2016。

费率差异和数量差异

您已经学习了如何创建年度每人成本和总人数的度量。您可以使用这些度量来创建费率差异和数量差异度量。

费率差异度量计算货币差异的哪一部分是由每人成本的差异造成的。
数量差异度量计算货币差异有多少是由人数的波动造成的。

创建预算费率差异度量

您可以按照以下步骤创建预算费率差异度量：

预算费率差异:=([预算年度CPH]/12-[实际年度CPH]/12)*[实际总人数]

创建预算数量差异度量

您可以按照以下步骤创建预算数量差异度量：

预算数量差异:=[预算总人数]*[预算年度CPH]/12

使用与预算差异度量分析数据

按如下方式创建Power PivotTable：

将“日期”表中的“财务季度”和“财务月份”字段添加到“行”。
将“实际年度CPH”、“预算年度CPH”、“预算费率差异”、“预算数量差异”、“预算总差异”度量添加到“值”。
将“日期”表中的“财务年度”字段和“账户”表中的“子类别”字段添加到“筛选器”。
在“财务年度”筛选器中选择FY2016。
在“子类别”筛选器中选择“人员”。
将“行标签”筛选为“财务季度”值为FY2016-Q1和FY2016-Q2。

您可以在上面的数据透视表中观察到以下内容：

显示的“预算总差异”值仅适用于“子类别”——“人员”。
对于财务季度FY2016-Q1，“预算总差异”为4,705,568美元，“预算费率差异”为970,506,297美元，“预算数量差异”为-965,800,727美元。
“预算费率差异”度量计算出970,506,297美元的预算差异（预算总差异）是由每人成本的差异造成的，而-965,800,727美元是由人数的差异造成的。
如果您将“预算费率差异”和“预算数量差异”相加，您将得到4,705,568美元，这与“人员”子类别“预算总差异”返回的值相同。
同样，对于财务季度FY2016-Q2，“预算费率差异”为1,281,467,662美元，“预算数量差异”为-1,210,710,978美元。如果您将“预算费率差异”和“预算数量差异”相加，您将得到70,756,678美元，这与数据透视表中显示的“预算总差异”值相同。

创建同比费率度量

您可以按照以下步骤创建同比费率度量：

同比费率:=([实际年度CPH]/12-[上一年实际年度CPH]/12)*[实际总人数]

创建同比数量度量

您可以按照以下步骤创建同比数量度量：

同比数量:=[同比实际总人数]*[上一年实际年度CPH]/12

创建预测费率差异度量

您可以按照以下步骤创建预测费率差异度量：

预测费率差异:=([预测年度CPH]/12-[实际年度CPH]/12)*[实际总人数]

创建预测数量差异度量

您可以按照以下步骤创建预测数量差异度量：

预测数量差异:=[预测总人数]*[预测年度CPH]/12

使用与预测差异度量分析数据

按如下方式创建Power PivotTable：

将“日期”表中的“财务季度”和“财务月份”字段添加到“行”。
将“实际年度CPH”、“预测年度CPH”、“预测费率差异”、“预测数量差异”、“预测总差异”度量添加到“值”。
将“日期”表中的“财务年度”字段和“账户”表中的“子类别”字段添加到“筛选器”。
在“财务年度”筛选器中选择FY2016。
在“子类别”筛选器中选择“人员”。
将“行标签”筛选为“财务季度”值为FY2016-Q1和FY2016-Q2。

创建预测预算费率差异度量

您可以按照以下步骤创建预测预算费率差异度量：

预测预算费率差异:=([预算年度CPH]/12-[预测年度CPH]/12)*[预测总人数]

创建预测预算数量差异度量

您可以按照以下步骤创建预测预算数量差异度量：

预测预算数量差异:=[预测预算总人数]*[预算年度CPH]/12

使用预测与预算差异度量分析数据

按如下方式创建Power PivotTable：

将“日期”表中的“财务季度”和“财务月份”字段添加到“行”。
将“预算年度CPH”、“预测年度CPH”、“预测预算费率差异”、“预测预算数量差异”、“预测预算总差异”度量添加到“值”。
将“日期”表中的“财务年度”字段和“账户”表中的“子类别”字段添加到“筛选器”。
在“财务年度”筛选器中选择FY2016。
在“子类别”筛选器中选择“人员”。
将“行标签”筛选为“财务季度”值为FY2016-Q1和FY2016-Q2。

Data with Forecast Variance Budget Measures

打印页面