使用 DAX 进行数据建模 – 快速指南
使用 DAX 进行数据建模 – 快速指南
使用 DAX 进行数据建模 – 概述
所有组织的决策者都已确定需要分析其组织的特定历史数据，以及整个行业的历史数据。在当今竞争激烈的世界中，为了应对不断变化的业务挑战，这一点变得日益重要。
大数据和商业智能已成为商业世界的流行语。数据源变得庞大，数据格式变得多样化。当下的需求是拥有简单易用的工具，以在更短的时间内处理不断流动的海量数据，从而在适当的时间获得洞察力并做出相关决策。
数据分析师不能再等待 IT 部门处理所需的数据。他们需要一个方便的工具，使他们能够快速理解所需的数据，并以帮助决策者在正确的时间采取所需行动的格式提供这些数据。
Microsoft Excel 有一个称为 Power Pivot 的强大工具，该工具在早期版本的 Excel 中作为加载项提供，并且是 Excel 2016 中的内置功能。 Power Pivot 的数据库称为数据模型和有效的公式语言上的数据模型，称为DAX（d ATA甲nalysis ë Xpressions的）使一个Excel用户执行任务，例如数据建模和在任何时间的分析。
在本教程中，您将学习使用 DAX 基于 Power Pivot 数据模型进行数据建模和分析。本教程中的插图使用了一个示例 Profit and Analysis 数据库。
数据建模和分析概念
您从不同来源获得的数据称为原始数据，需要先进行处理，然后才能将其用于分析目的。您将在“数据建模和分析概念”一章中了解这些内容。
使用 Excel Power Pivot 进行数据建模和分析
由于您将在本教程中掌握的工具是 Excel Power Pivot，因此您需要了解如何在 Power Pivot 中执行数据建模和分析步骤。您将在“使用 Excel Power Pivot 进行数据建模和分析”一章中更广泛地了解这些内容。
随着您继续阅读后续章节，您将了解 Power Pivot、DAX 和 DAX 函数在数据建模和分析中的不同方面。
在本教程结束时，您将能够使用 DAX 对手头的任何上下文执行数据建模和分析。
使用 DAX 进行数据建模 – 概念
商业智能 (BI) 在多个领域和组织中变得越来越重要。在竞争日益激烈的世界中，基于历史数据的决策和预测变得至关重要。任何类型的数据分析都可以从内部和外部获得大量来自不同来源的数据。
然而，挑战在于根据当前的需求从可用的大数据中提取相关数据，并以一种友好的方式存储它，以便从数据中投射出不同的见解。这样通过使用关键业务术语获得的数据模型是一种有价值的交流工具。数据模型还需要提供一种根据需要快速生成报告的方法。
BI 系统的数据建模使您能够应对许多数据挑战。
BI 数据模型的先决条件
BI 的数据模型应满足对其进行数据分析的业务的要求。以下是任何数据模型必须满足的最低限度的基础知识 –
数据模型需要特定于业务
适用于一个业务线的数据模型可能不适用于不同的业务线。因此，必须根据特定业务、使用的业务术语、数据类型及其关系来开发数据模型。它应该基于组织中做出的目标和决策类型。
数据模型需要内置智能
数据模型应包括通过元数据、层次结构和继承的内置智能，以促进高效和有效的商业智能流程。有了这个，您将能够为不同的用户提供一个通用平台，消除重复的过程。
数据模型需要健壮
数据模型应该准确地呈现特定于业务的数据。它应该启用有效的磁盘和内存存储，以便于快速处理和报告。
数据模型需要可扩展
数据模型应该能够以快速有效的方式适应不断变化的业务场景。可能必须包含新数据或新数据类型。可能必须有效地处理数据刷新。
BI 数据建模
BI 的数据建模包括以下步骤 –
- 塑造数据
- 加载数据
- 定义表之间的关系
- 定义数据类型
- 创造新的数据洞察力
塑造数据
构建数据模型所需的数据可以来自各种来源，并且可以采用不同的格式。您需要确定特定数据分析需要来自每个数据源的数据的哪一部分。这称为塑造数据。
例如，如果您要检索组织中所有员工的数据，则需要确定每个员工的哪些详细信息与当前上下文相关。换句话说，您需要确定需要导入employee 表的哪些列。这是因为，数据模型中表中的列数越少，表上的计算速度就越快。
加载数据
您需要加载已识别的数据 – 数据表以及每个表中选定的列。
定义表之间的关系
接下来，您需要定义各种表之间的逻辑关系，以便于组合这些表中的数据，即如果您有一个表 – 产品 – 包含有关产品的数据和一个表 – 销售 – 与产品的各种销售交易，通过定义两个表之间的关系，您可以总结销售，产品明智。
定义数据类型
为数据模型中的数据确定适当的数据类型对于计算的准确性至关重要。对于您导入的每个表中的每一列，您需要定义数据类型。例如文本数据类型、实数数据类型、整数数据类型等。
创造新的数据洞察力
这是 BI 日期建模的关键步骤。构建的数据模型可能必须与需要了解数据趋势并在很短的时间内做出所需决策的几个人共享。因此，从源数据创建新的数据洞察将是有效的，避免对分析进行返工。
新的数据洞察可以采用元数据的形式，便于特定业务人员理解和使用。
数据分析
一旦数据模型准备就绪，就可以根据需要对数据进行分析。呈现分析结果也是一个重要步骤，因为将根据报告做出决定。
使用 Excel Power Pivot 进行数据建模
Microsoft Excel Power Pivot 是一款出色的数据建模和分析工具。
-
数据模型是 Power Pivot 数据库。
-
DAX 是一种公式语言，可用于通过 DAX 公式使用数据模型中的数据创建元数据。
-
使用数据模型中的数据和元数据创建的 Excel 中的 Power PivotTables 使您能够分析数据并呈现结果。
在本教程中，您将学习使用 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 数据透视表来分析数据，这些数据透视表基于在数据透视表字段列表中显示为字段的表和度量中的列。
添加计算列
表中的计算列是您使用 DAX 公式添加到表中的列。
您将在“扩展数据模型”一章中学习如何在数据模型的表中添加计算列。
创建日期表
要在 DAX 公式中使用时间智能函数来创建元数据，您需要一个日期表。如果您不熟悉日期表，请参阅章节 – 了解日期表。
您将在“扩展数据模型”一章中学习如何在数据模型中创建日期表。
创建度量
您可以根据当前上下文中数据分析的需要，使用 DAX 函数和 DAX 公式在数据表中创建各种度量，以进行不同的计算。
这是使用 DAX 进行数据建模的关键步骤。
您将在后续章节中学习如何为损益分析的各种目的创建度量。
使用 Power PivotTables 分析数据
您可以为损益分析的每个方面创建 Power 数据透视表。在后续章节中学习如何使用 DAX 创建度量时，您还将学习如何使用 Power PivotTables 使用这些度量分析数据。
将数据加载到数据模型中
您可以将来自不同类型数据源的数据加载到数据模型中。为此，您可以在 Power Pivot 窗口功能区的获取外部数据组中找到各种选项。
如您所见，您可以从数据库、数据服务或其他几种类型的数据源加载数据。
当您将数据从数据源加载到数据模型中时，将与数据源建立连接。这会在源数据更改时启用数据刷新。
使用新的数据模型启动
在本节中，您将学习如何为损益分析数据建模。用于分析的数据位于 Microsoft Access 数据库中。
您可以按如下方式启动新的数据模型 –
- 打开一个新的 Excel 工作簿
- 单击功能区上的 PowerPivot 选项卡
- 单击数据模型组中的管理
出现 Power Pivot 窗口。由于您尚未加载任何数据，因此该窗口将为空白。
将数据从 Access 数据库加载到数据模型中
要从 Access 数据库加载数据，请执行以下步骤 –
- 单击功能区上“获取外部数据”组中的“从数据库”。
- 单击下拉列表中的从访问。
出现表导入向导对话框。
-
浏览到 Access 文件。
-
为连接提供一个友好的名称。
-
单击下一步按钮。表导入向导的下一部分出现。
-
在表导入向导中，选择选项 – 从表和视图列表中选择以选择要导入的数据。
-
单击下一步按钮。表导入向导的下一部分如下面的屏幕截图所示。
-
选择所有表。
-
给表格起友好的名字。这是必要的，因为这些名称出现在 Power 数据透视表中，因此每个人都应该理解。
选择表中的列
对于当前分析，您可能不需要选定表中的所有列。因此，您只需要选择在塑造数据时选择的那些列。
-
单击预览和过滤按钮。表导入向导的下一部分 – 所选表的预览 – 出现。
-
如上图所示，列标题有复选框。在所选表中选择要导入的列。
-
单击确定。对其他表重复相同的操作。
将数据导入数据模型
您处于将数据加载到数据模型的最后阶段。单击表导入向导中的完成按钮。表导入向导的下一部分出现。
将显示导入状态。数据加载完成后，状态最终显示成功。
查看数据模型中的数据
导入的表出现在 Power Pivot 窗口中。这是数据模型的视图
您可以观察以下内容 –
- 每个表都出现在单独的选项卡中。
- 选项卡名称是各自的表名称。
- 数据下方的区域用于计算。
查看连接名称
单击获取外部数据组中的现有连接。现有连接对话框出现，如下面的屏幕截图所示。
如上图所示，给定的连接名称出现在 PowerPivot Data Connections 下。
在数据模型中定义数据类型
在 Power Pivot 数据模型中，列中的整个数据必须具有相同的数据类型。为了完成准确的计算，您需要确保数据模型中每个表中每一列的数据类型符合要求。
数据模型中的表
在上一章创建的数据模型中，有 3 个表 –
- 帐户
- 地理定位
- 财务数据
确保适当的数据类型
为确保表中的列符合要求，您需要在 Power Pivot 窗口中检查它们的数据类型。
-
单击表中的列。
-
请注意格式组中功能区上显示的列的数据类型。
如果所选列的数据类型不合适，请按如下方式更改数据类型。
-
单击格式组中数据类型旁边的向下箭头。
-
在下拉列表中单击适当的数据类型。
-
对数据模型中所有表中的每一列重复此操作。
帐户表中的列
在帐户表中，您有以下列 –
| Sr.No | 列和描述 |
|---|---|
| 1 | 帐户
每行包含一个帐号。该列具有唯一值，用于定义与财务数据表的关系。 |
| 2 | 班级
与每个帐户关联的类。示例 – 费用、净收入等。 |
| 3 | 子类
描述费用或收入的类型。例子——人。 |
Accounts 表中的所有列本质上都是描述性的，因此都是文本数据类型。
Geography Locn 表中的列
Geography Locn 表包含有关每个利润中心的数据。
利润中心列的每一行都包含一个利润中心标识。此列具有唯一值，用于定义与财务数据表的关系。
财务数据表中的列
在财务数据表中，您有以下列 –
| Column | 描述 | 数据类型 |
|---|---|---|
| Fiscal Month | 月份和年份 | 文本 |
| Profit Center | 利润中心标识 | 文本 |
| Account |
账号。 每个帐户可以有多个利润中心。 |
文本 |
| Budget | 每个利润中心的每月预算金额。 | 货币 |
| Actual | 每个利润中心的每月实际金额。 | 货币 |
| Forecast | 每个利润中心的每月预测金额。 | 货币 |
| Actual People | 每个人帐户的每个利润中心的月末实际员工人数。 | 完整的号码 |
| Budget People | 每个人帐户的每个利润中心的月末预算员工人数。 | 完整的号码 |
| Forecast People | 每个人帐户的每个利润中心的月末预测员工人数。 | 完整的号码 |
数据模型中的表类型
Accounts 表和 Geography Locn 表都是维度表，也称为查找表。
财务数据表是事实表，也称为数据表。财务数据表包含利润和分析计算所需的数据。您还将在此财务数据表中以度量和计算列的形式创建元数据，以便在继续本教程时为各种类型的损益计算建模数据。
了解数据表
数据分析涉及随时间浏览数据和跨时间段进行计算。例如，您可能必须将当前年度的利润与前一年的利润进行比较。同样，您可能必须预测未来几年的增长和利润。对于这些，您需要在一段时间内使用分组和聚合。
DAX 提供了多个时间智能函数，可帮助您执行大多数此类计算。但是，这些 DAX 函数需要一个日期表才能与数据模型中的其他表一起使用。
您可以将日期表与数据源中的其他数据一起导入，也可以自己在数据模型中创建日期表。
在本章中，您将了解日期表的不同方面。如果您熟悉 Power Pivot 数据模型中的日期表，则可以跳过本章并继续阅读后续章节。否则，您可以了解 Power Pivot 数据模型中的日期表。
什么是日期表？
日期表是数据模型中的表，在所需的持续时间内至少有一列连续的日期。它可以有代表不同时间段的附加列。但是，根据 DAX 时间智能函数的要求，需要的是连续日期列。
例如，
-
日期表可以包含日期、财政月、财政季度和财政年度等列。
-
日期表可以包含日期、月份、季度和年份等列。
具有连续日期的日期表
假设您需要在一个日历年的范围内进行计算。然后，Date 表必须至少有一个包含一组连续日期的列，包括该特定日历年中的所有日期。
例如，假设您要浏览的数据的日期为 2014 年 4 月 1 日至 11 月 30 日th，2016 年。
-
如果您必须报告日历年，则需要一个日期表，其中包含一列 – 日期，其中包含从 1 月 1 日开始的所有日期st, 2014 年至 12 月 31 日st, 2016 年。
-
如果您必须报告一个财政年度，并且您的财政年度结束是 30th 六月，您需要一个日期表，其中有一列 – 日期，其中包含从 7 月 1 日开始的所有日期st, 2013 年至 6 月 30 日th, 2017 年。
-
如果您必须同时报告日历和会计年度，那么您可以拥有一个跨越所需日期范围的日期表。
您的日期表必须包含给定持续时间内每年范围内的所有天数。因此，您将在该时间段内获得连续的日期。
如果您定期用新数据刷新数据，结束日期将延长一两年，这样您就不必经常更新日期表。
日期表类似于以下屏幕截图。
向数据模型添加日期表
您可以通过以下任何一种方式将日期表添加到数据模型中 –
-
从关系数据库或任何其他数据源导入。
-
在 Excel 中创建日期表，然后在 Power Pivot 中复制或链接到新表。
-
从 Microsoft Azure 市场导入。
在 Excel 中创建日期表并复制到数据模型
在 Excel 中创建日期表并复制到数据模型是在数据模型中创建数据表的最简单和最灵活的方法。
-
在 Excel 中打开一个新工作表。
-
类型 – 列的第一行中的日期。
-
在同一列的第二行中键入要创建的日期范围中的第一个日期。
-
选择单元格，单击填充手柄并将其向下拖动以在所需日期范围内创建一列连续日期。
例如，键入 1/1/2014，单击填充手柄并向下拖动以填充直到 31/12/2016 的连续日期。
- 单击日期列。
- 单击功能区上的插入选项卡。
- 单击表。
- 验证表范围。
- 单击确定。
单列日期的表格已在 Excel 中准备就绪。
- 选择表。
- 单击功能区上的复制。
- 单击 Power Pivot 窗口。
- 单击功能区上的粘贴。
这会将剪贴板的内容添加到数据模型中的新表中。因此，您也可以使用相同的方法在现有数据模型中创建日期表。
出现粘贴预览对话框，如下面的屏幕截图所示。
- 在表名称框中键入日期。
- 预览数据。
- 选中复选框 – 使用第一行作为列标题。
- 单击确定。
这会将剪贴板的内容复制到数据模型中的新表中。
现在，您在数据模型中有一个日期表，其中包含一列连续日期。该列的标题是您在 Excel 表中给出的日期。
向日期表添加新日期列
接下来，您可以根据计算要求将计算列添加到日期表。
例如，您可以添加列 – 日、月、年和季度，如下所示 –
- 日
=DAY(‘日期'[日期])
- 月
=MONTH(‘日期'[日期])
- 年
=YEAR(‘日期'[日期])
- 25美分硬币
=CONCATENATE (“QTR”, INT ((‘Date'[Month]+2)/3))
数据模型中生成的日期表类似于以下屏幕截图。
因此，您可以向日期表中添加任意数量的计算列。重要且必需的是，日期表必须有一列连续的日期，跨越您执行计算的持续时间。
为日历年创建日期表
日历年通常包括一年的 1 月 1 日至 12 月 31 日的日期，还包括为该特定年份标记的假期。执行计算时，您可能只需要考虑工作日，周末和节假日除外。
假设您要为日历年 2017 创建日期表。
-
创建一个带有日期列的 Excel 表，由 1 的连续日期组成st 2017年1月至31日st 2017 年 12 月。（请参阅上一节了解如何执行此操作。）
-
复制 Excel 表并将其粘贴到数据模型中的新表中。（请参阅上一节了解如何执行此操作。）
-
将表命名为日历。
-
添加以下计算列 –
-
Day =DAY(‘日历'[日期])
-
月 =MONTH(‘日历'[日期])
-
Year =YEAR(‘日历'[日期])
-
星期几 =FORMAT(‘Calendar'[Date],”DDD”)
-
月份名称 =FORMAT(‘日历'[日期],”MMM”)
-
将假期添加到日历表
将假期添加到日历表如下 –
-
获取当年宣布的假期列表。
-
例如，对于美国，您可以从以下链接http://www.calendar-365.com/获取任何所需年份的假期列表。
-
将它们复制并粘贴到 Excel 工作表中。
-
复制 Excel 表并将其粘贴到数据模型中的新表中。
-
将表命名为 Holidays。
-
接下来，您可以使用 DAX LOOKUPVALUE 函数将计算出的假期列添加到日历表中。
=LOOKUPVALUE(假期[假期],假期[日期],’日历'[日期])
DAX LOOKUPVALUE 函数在第二个参数，即Holidays[Date] 中搜索第三个参数，即Calendar[Date]，如果有匹配，则返回第一个参数，即Holidays[Holiday]。结果将如下面的屏幕截图所示。
向会计年度添加列
一个财政年度通常包括从 1st会计年度结束后至下一会计年度结束的月份。例如，如果会计年度结束是 31st 3 月，则财政年度范围从 1st 4 月至 31 日st 行进。
您可以使用 DAX 公式在日历表中包含会计时间段 –
-
为 FYE 添加度量
财年：=3
-
添加以下计算列 –
-
财政年度
=IF(‘日历'[月]<=’日历'[FYE],’日历'[年],’日历'[年]+1)
-
财政月
=IF(‘Calendar'[Month]<=’Calendar'[FYE],12-‘Calendar'[FYE]+’Calendar'[Month],’Calendar'[Month]-‘Calendar'[FYE])
-
财政季度
=INT((‘日历'[财政月]+2)/3)
-
设置日期表属性
当您使用 TOTALYTD、PREVIOUSMONTH 和 DATESBETWEEN 等 DAX 时间智能函数时，它们需要元数据才能正常工作。日期表属性设置此类元数据。
设置日期表属性 –
- 在 Power Pivot 窗口中选择日历表。
- 单击功能区上的设计选项卡。
- 单击日历组中的标记为日期表。
- 单击下拉列表中的标记为日期表。
出现标记为日期表对话框。在日历表中选择日期列。这必须是 Date 数据类型的列，并且必须具有唯一值。单击确定。
扩展数据模型
在本章中，您将学习如何扩展在前几章中创建的数据模型。扩展数据模型包括 –
- 添加表格
- 在现有表中添加计算列
- 在现有表中创建度量
其中，创建度量至关重要，因为它涉及在数据模型中提供新的数据洞察力，这将使使用数据模型的人避免返工并在分析数据和决策时节省时间。
由于损益分析涉及处理时间段，并且您将使用 DAX 时间智能函数，因此您需要数据模型中的日期表。
如果您不熟悉日期表，请阅读一章 – 了解日期表。
您可以按如下方式扩展数据模型 –
-
要创建数据表（即财务数据表和日期表）之间的关系，您需要在财务数据表中创建一个计算列日期。
-
要执行不同类型的计算，您需要在数据表 – Finance Data 和查找表 – Accounts and Geography Locn 之间创建关系。
-
您需要创建各种度量来帮助您执行多项计算并执行所需的分析。
这些步骤实质上构成了使用数据模型进行损益分析的数据建模步骤。但是，这是要使用 Power Pivot 数据模型执行的任何类型的数据分析的步骤序列。
此外，您将在后续章节中学习如何创建度量以及如何在 Power 数据透视表中使用它们。这将使您充分了解使用 DAX 进行数据建模和使用 Power PivotTables 进行数据分析。
向数据模型添加日期表
为跨越财政年度的时间段创建一个日期表，如下所示 –
-
在新的 Excel 工作表中创建一个带有标题的单列的表格 – 日期和范围从 7/1/2011 到 6/30/2018 的连续日期。
-
从 Excel 复制表格并将其粘贴到 Power Pivot 窗口中。这将在 Power Pivot 数据模型中创建一个新表。
-
将表命名为日期。
-
确保日期表中的日期列是数据类型 – 日期 (DateTime)。
接下来，您需要将计算列 – 财政年度、财政季度、财政月份和月份添加到日期表中，如下所示 –
财政年度
假设财政年度结束是 6 月 30 日th. 那么，一个财政年度从 1st 7 月至 30 日th六月。例如，7 月 1 日期间st, 2011 (7/1/2011) 至 6 月 30th, 2012 (6/30/2012) 将是 2012 财年。
在 Date 表中，假设您要表示与 FY2012 相同的内容。
-
您需要先提取 Date 的财政年度部分，并在其后附加 FY。
-
对于 2011 年 7 月至 2011 年 12 月的日期，财政年度为 1+2011。
-
对于 2012 年 1 月至 2012 年 6 月的日期，财政年度为 0+2012。
-
概括地说，如果财政年度结束月份是 FYE，请执行以下操作 –
((Month – 1)/FYE) + Year 的整数部分
-
接下来，取最右边的 4 个字符获得财政年度。
-
-
在 DAX 中，您可以表示与 –
RIGHT(INT((MONTH(‘Date'[Date])-1)/’Date'[FYE])+YEAR(‘Date'[Date]),4)
-
使用 DAX 公式在日期表中添加计算列 Fiscal Year –
=”FY”&RIGHT(INT((MONTH(‘Date'[Date])-1)/’Date'[FYE])+YEAR(‘Date'[Date]),4)
财政季度
如果 FYE 代表财政年度结束的月份，则财政季度为
(((Month+FYE-1)/12) + 3)/3) 的整数部分
-
在 DAX 中，您可以表示与 –
INT((MOD(MONTH(‘Date'[Date])+’Date'[FYE]-1,12)+3)/3)
-
使用 DAX 公式在日期表中添加计算列 Fiscal Quarter –
=’Date'[FiscalYear]&”-Q”&FORMAT(INT((MOD(MONTH(‘Date'[Date]) + ‘Date'[FYE]-1,12) + 3)/3), “0” )
财政月
如果 FYE 代表财政年度结束，则财政月期间为
((Month+FYE-1)/12 的剩余部分) + 1
-
在 DAX 中，您可以表示与 –
MOD(MONTH(‘Date'[Date])+’Date'[FYE]-1,12)+1
-
使用 DAX 公式在日期表中添加计算列 Fiscal Month –
=’日期'[财政年度]&”-P” & FORMAT(MOD(MONTH([Date])+[FYE]-1,12)+1,”00″)
月
最后，添加代表财政年度月份数的计算列 Month，如下所示 –
=FORMAT(MOD(MONTH([Date])+[FYE]-1,12)+1,”00″) & “-” & FORMAT([Date],”mmm”)
生成的日期表类似于以下屏幕截图。
将表 – 日期标记为日期表，将列 – 日期标记为具有唯一值的列，如下面的屏幕截图所示。
添加计算列
要在财务数据表和日期表之间创建关系，您需要在财务数据表中有一列日期值。
-
使用 DAX 公式在财务数据表中添加计算列日期 –
= DATEVALUE（’财务数据'[财政月]）
定义数据模型中表之间的关系
您在数据模型中有以下表格 –
- 数据表 – 财务数据
- 查找表 – Accounts and Geography Locn
- 日期表 – 日期
要定义数据模型中表之间的关系，以下是步骤 –
-
在 Power Pivot 的图表视图中查看表格。
-
在表之间创建以下关系 –
-
财务数据表和帐户表与列帐户之间的关系。
-
Finance Data 表和 Geography Locn 表与 Profit Center 列之间的关系。
-
财务数据表和日期表与日期列之间的关系。
-
从客户端工具隐藏列
如果数据表中有任何列不会用作任何数据透视表中的字段，则可以将它们隐藏在数据模型中。然后，它们在数据透视表字段列表中将不可见。
在财务数据表中，您有 4 列 – 会计月份、日期、帐户和利润中心，您不会将它们用作任何数据透视表中的字段。因此，您可以隐藏它们，以便它们不会出现在数据透视表字段列表中。
-
在财务数据表中选择列 – 会计月份、日期、帐户和利润中心。
-
右键单击并在下拉列表中选择从客户端工具中隐藏。
在表中创建度量
您已准备好使用数据模型和 Power PivotTable 使用 DAX 进行数据建模和分析。
在后续章节中，您将学习如何创建度量以及如何在 Power PivotTables 中使用它们。您将在数据表（即财务数据表）中创建所有度量。
您将使用数据表 – 财务数据中的 DAX 公式创建度量，您可以在任意数量的数据透视表中使用它进行数据分析。度量本质上是元数据。在数据表中创建度量是数据建模的一部分，在 Power PivotTables 中汇总它们是数据分析的一部分。
基础财务措施和分析
您可以在数据模型中创建各种度量以用于任意数量的 Power 数据透视表。这与使用 DAX 的数据模型形成了数据建模和分析过程。
正如您在前面部分中学到的那样，数据建模和分析取决于特定的业务和上下文。在本章中，您将学习基于示例损益数据库的数据建模和分析，以了解如何创建所需的度量并在各种 Power 数据透视表中使用它们。
您可以对任何业务和上下文应用相同的方法进行数据建模和分析
创建基于财务数据的度量
要创建任何财务报告，您需要计算特定时间段、组织、帐户或地理位置的金额。您还需要执行人数和每个人数的成本计算。在数据模型中，您可以创建可以在创建其他度量时重用的基本度量。这是使用 DAX 进行数据建模的有效方法。
为了执行损益数据分析的计算，您可以创建诸如总和、同比、年初至今、季度至今、差异、员工人数、人均成本等度量。您可以在 Power PivotTables 中使用这些度量来分析数据并报告分析结果。
在以下部分中，您将学习如何创建基础财务度量并使用这些度量分析数据。这些度量被称为基础度量，因为它们可用于创建其他财务度量。您还将学习如何为以前的时间段创建度量并在分析中使用它们。
创建基础财务措施
在财务数据分析中，预算和预测起着主要作用。
预算
预算是对公司一个财政年度的收入和支出的估计。预算是在财政年度开始时计算的，同时考虑到公司的目标和指标。由于市场状况可能会发生变化，公司可能必须根据行业当前趋势调整其目标和指标，因此需要在财政年度不时分析预算措施。
预报
财务预测是通过检查公司的收入和支出的历史数据来估计公司未来的财务结果。您可以在以下方面使用财务预测 –
-
确定如何分配未来期间的预算。
-
跟踪公司的预期业绩。
-
及时做出决定以解决目标的不足，或最大限度地利用新出现的机会。
实际情况
要执行预算和预测计算，您需要任何时间点的实际收入和支出。
您可以创建以下 3 个基本财务指标，可用于在数据模式下创建其他财务指标 –
- 预算总额
- 实际金额
- 预测总和
这些度量是财务数据表中的预算、实际和预测列的聚合总和。
创建基本财务措施如下 –
预算总额
预算总和：=SUM(‘财务数据'[预算])
实际金额
实际总和：=SUM(‘财务数据'[实际])
预测总和
预测总和:=SUM(‘财务数据'[预测])
使用基本财务指标分析数据
使用基本财务指标和日期表，您可以按如下方式进行分析 –
- 创建 Power 数据透视表。
- 将“日期”表中的“会计年度”字段添加到“行”。
- 将度量预算总和、实际总和和预测总和（在数据透视表字段列表中显示为字段）添加到值。
为以前期间创建财务度量
使用三个基本财务度量和日期表，您可以创建其他财务度量。
假设您想将一个季度的实际总和与上一季度的实际总和进行比较。您可以创建度量 – 上一季度实际总和。
上一季度实际金额：=CALCULATE([实际金额], DATEADD(‘Date'[Date],1,QUARTER))
同样，您可以创建度量 – 上一年实际金额。
上一年实际金额：=CALCULATE([实际金额], DATEADD(‘Date'[Date],1,YEAR))
使用财务指标分析以前时期的数据
使用基本度量、先前期间的度量和日期表，您可以按如下方式执行分析 –
- 创建 Power 数据透视表。
- 将字段财政季度从日期表添加到行。
- 将度量实际总和和上一季度实际总和添加到值。
- 创建另一个 Power 数据透视表。
- 将“日期”表中的“会计年度”字段添加到“行”。
- 将度量实际总和和上一年实际总和添加到值中。
同比财务指标和分析
同比 (YoY) 是衡量增长的指标。它是通过从实际总和中减去上一年的实际总和而获得的。
如果结果为正，则反映实际增加，如果结果为负，则反映实际减少，即如果我们按年计算 –
同比=（实际金额-上年实际金额）
- 如果实际金额 > 上一年的实际金额，则同比为正。
- 如果实际总和 < 上一年的实际总和，则同比将为负。
在财务数据中，诸如费用科目之类的科目将具有借方（正）金额，而收入科目将具有贷方（负）金额。因此，对于费用账户，上述公式工作正常。
但是，对于收入账户，应该是相反的，即
- 如果实际金额 > 上一年的实际金额，则同比应为负数。
- 如果实际总和 < 上一年的实际总和，则同比应为正。
因此，对于收入帐户，您必须按年计算 –
同比= -（实际金额-上年实际金额）
创建同比度量
您可以使用以下 DAX 公式创建同比度量 –
YoY:=IF(CONTAINS(Accounts, Accounts[Class],”Net Revenue”),-([Actual Sum]-[Prior Year Actual Sum]), [Actual Sum]-[Prior Year Actual Sum])
在上面的 DAX 公式中 –
-
DAX CONTAINS 函数返回 TRUE，如果行在 Accounts 表的列类中有“净收入”。
-
DAX IF 函数然后返回 –([实际金额]-[上一年实际金额])。
-
否则，DAX IF 函数返回 [Actual Sum]-[Prior Year Actual Sum]。
创建同比百分比度量
您可以将同比表示为百分比 –
(YoY) / (上年实际金额)
您可以使用以下 DAX 公式创建同比百分比度量 –
YoY %:=IF([上一年实际金额], [YoY] / ABS([上一年实际金额]),BLANK())
上面的公式中使用了 DAX IF 函数来确保没有被零除。
使用年度指标分析数据
创建一个 Power PivotTable 如下 –
- 将 Accounts 表中的 Class 和 Sub Class 字段添加到 Rows。
- 将度量 – 实际金额、上一年实际金额、YoY 和 YoY % 添加到值。
- 在“日期”表中的字段 Fiscal Year 上插入切片器。
- 在切片器中选择 FY2016。
创建预算同比度量
您可以按如下方式创建预算同比措施 –
预算同比：= IF(CONTAINS(Accounts,Accounts[Class],”Net Revenue”), – ([Budget Sum] – [Prior Year Actual Sum]), [Budget Sum] – [Prior Year Actual Sum])
创建预算同比百分比度量
您可以创建预算同比百分比措施如下 –
预算同比 %:=IF([上一年实际金额] , [预算同比] / ABS ([上一年实际金额]) , BLANK())
使用预算年度指标分析数据
创建一个 Power PivotTable 如下 –
- 将 Accounts 表中的 Class 和 Sub Class 字段添加到 Rows。
- 将度量 – 预算总和、上一年实际总和、预算同比和预算同比百分比添加到值中。
- 在“日期”表中的字段 Fiscal Year 上插入切片器。
- 在切片器中选择 FY2016。
创建预测同比度量
您可以创建预测同比措施如下 –
预测同比：=IF(CONTAINS(Accounts,Accounts[Class],”Net Revenue”), – ([Forecast Sum] – [Prior Year Actual Sum]), [Forecast Sum] – [Prior Year Actual Sum])
创建预测同比百分比度量
您可以创建预测同比百分比措施如下 –
预测同比%:=IF([上一年实际金额],[预测同比]/ABS([上一年实际金额]),BLANK())
使用预测年度指标分析数据
创建一个 Power PivotTable 如下 –
- 将 Accounts 表中的 Class 和 Sub Class 字段添加到 Rows。
- 将度量 – 预测总和、上一年实际总和、预测同比和预测同比百分比添加到值中。
- 在“数据”表中的字段 Fiscal Year 上插入切片器。
- 在切片器中选择 FY2016。
方差测量和分析
您可以创建差异度量，例如预算差异、预测差异和预测预算差异。您还可以根据这些度量分析财务数据。
为预算总和度量创建差异
创建预算总和度量（VTB Sum）的差异如下 –
VTB 总和：=[预算总和]-[实际总和]
创建预算百分比度量的差异
创建预算百分比度量（VTB %）的差异如下 –
VTB %:=IF([预算总和],[VTB总和]/ABS([预算总和]),BLANK())
分析具有预算度量差异的数据
创建一个 Power PivotTable 如下 –
- 将“会计年度”从“日期”表添加到“行”。
- 将财务数据表中的度量实际总和、预算总和、VTB 总和、VTB % 添加到值。
创建方差以预测总和度量
创建预测总和（VTF Sum）度量的方差如下 –
VTF 总和：=[预测总和]-[实际总和]
创建方差以预测百分比度量
创建方差以预测百分比度量（VTF %）如下 –
VTF %:=IF([预测总和],[VTF 总和]/ABS([预测总和]),BLANK())
分析数据与预测措施的差异
创建一个 Power PivotTable 如下 –
- 将“会计年度”从“日期”表添加到“行”。
- 将财务数据表中的度量实际总和、预测总和、VTF 总和、VTF % 添加到值。
创建预算总和度量的预测差异
创建预算总和的预测方差（预测 VTB 总和）度量如下 –
预测 VTB 总和：=[预算总和]-[预测总和]
创建预算百分比度量的预测差异
创建预测差异预算百分比（预测 VTB 百分比）度量如下 –
预测 VTB %:=IF([预算总和],[预测 VTB 总和]/ABS([预算总和]),BLANK())
使用预算度量的预测差异分析数据
创建一个 Power PivotTable 如下 –
- 将“会计年度”从“日期”表添加到“行”。
- 将财务数据表中的度量预算总和、预测总和、预测 VTB 总和、预测 VTB % 添加到值。
年初至今的测量和分析
要计算包含从期间开始（例如会计年度）到特定时间段的起始余额的结果，您可以使用 DAX 时间智能函数。这将使您能够分析月级别的数据。
在本章中，您将学习如何创建 Year-to-Date 度量以及如何使用该度量进行数据分析。
创建年初至今的实际金额度量
创建年初至今实际金额度量如下 –
YTD 实际金额:=TOTALYTD([实际金额], ‘Date'[Date], ALL(‘Date’), “6/30”)
创建年初至今的预算总和度量
创建年初至今的预算总和措施如下 –
YTD 预算总和:=TOTALYTD([预算总和], ‘Date'[Date], ALL(‘Date’), “6/30”)
创建年初至今预测总和度量
创建年初至今的预测总和措施如下 –
YTD Forecast Sum:=TOTALYTD([Forecast Sum], ‘Date'[Date], ALL(‘Date’), “6/30”)
创建上一个年初至今的实际金额度量
创建上一个年初至今实际金额度量如下 –
上一年初至今实际总和：=TOTALYTD([上一年实际总和], ‘日期'[日期], ALL(‘日期’), “6/30”)
使用年初至今的度量分析数据
创建一个 Power PivotTable 如下 –
-
将日期表中的月份添加到行。
-
将财务数据表中的度量 Actual Sum、YTD Actual Sum、YTD Budget Sum 和 YTD Forecast Sum 添加到值。
-
在日期表中的会计年度上插入切片器。
-
在切片器中选择 FY2016。
创建一个 Power PivotTable 如下 –
-
将日期表中的月份添加到行。
-
将财务数据表中的度量实际总和、YTD 实际总和、上一年实际总和和上一年 YTD 实际总和添加到值。
-
在日期表中的会计年度上插入切片器。
-
在切片器中选择 FY2016。
季度至今的衡量和分析
要计算包含从期间开始（例如会计季度）到特定时间段的起始余额的结果，您可以使用 DAX 时间智能函数。这将使您能够分析月级别的数据。
在本章中，您将学习如何创建季度至今测度以及如何使用该测度进行数据分析。
创建季度至今总和度量
创建季度至今实际总和度量如下 –
QTD 实际总和：=TOTALQTD([实际总和],’日期'[日期],ALL(‘日期’))
创建季度至今的预算总和度量
创建季度迄今预算总和措施如下 –
QTD 预算总和：=TOTALQTD([预算总和], ‘Date'[Date], ALL(‘Date’))
创建季度至今预测总和度量
创建季度迄今预算总和措施如下 –
QTD 预算总和：=TOTALQTD([预算总和], ‘Date'[Date], ALL(‘Date’))
创建季度至今预测总和度量
创建季度至今预测总和措施如下 –
QTD 预测总和:=TOTALQTD([预测总和], ‘日期'[日期], ALL(‘日期’))
创建上一季度至今的实际金额度量
创建上一季度至今的实际金额度量如下 –
前一季度实际总和：=TOTALQTD([前一季度实际总和], ‘日期'[日期], ALL(‘日期’))
使用季度至今的度量分析数据
创建一个 Power PivotTable 如下 –
-
将会计月份从日期表添加到行。
-
将财务数据表中的度量实际总和、QTD 实际总和、QTD 预算总和和 QTD 预测总和添加到值。
-
在日期表中的财政季度插入一个切片器。
-
在切片器中选择 FY2016-Q2。
创建一个 Power PivotTable 如下 –
-
将会计月份从日期表添加到行。
-
将财务数据表中的度量 Actual Sum、QTD Actual Sum、Prior Quarter Actual Sum 和 Prior QTD Actual Sum 添加到值。
-
在日期表中的财政季度插入切片器。
-
在切片器中选择 FY2016-Q1。
预算措施和分析
预算涉及估计公司在一个财政年度的现金流量。公司的财务状况、其目标、预期收入和费用在预算中都被考虑在内。
然而，本财政年度的市场状况可能会发生变化，公司可能不得不重新设定其目标。这需要使用财政年度开始时估计的预算（Budget Sum）和从财政年度开始至今的实际支出金额（YTD Actual Sum）来分析财务数据。
在财政年度的任何时候，您都可以计算以下内容 –
未用余额
未支出余额是实际支出后剩余的预算，即
未用余额 = 年初至今预算总额 – 年初至今实际总额
预算完成率 %
Budget Attainment % 是您迄今为止花费的预算的百分比，即
预算实现百分比 = YTD 实际金额/YTD 预算金额
这些计算有助于那些使用预算编制的公司做出决策。
创建未支出余额度量
您可以按如下方式创建未支出余额度量 –
未用余额：=CALCULATE([YTD Budget Sum],ALL(‘Finance Data'[Date]) )-[YTD Actual Sum]
创建预算实现百分比度量
您可以创建预算实现百分比措施如下 –
预算实现 %:=IF([YTD 预算总和],[YTD 实际总和]/CALCULATE([YTD 预算总和],ALL(‘财务数据'[日期])),BLANK())
使用预算度量分析数据
创建一个 Power PivotTable 如下 –
-
将 Date 表中的 Month 添加到 Rows。
-
将财务数据表中的度量预算总和、YTD 预算总和、YTD 实际总和、预算实现百分比和未支出余额添加到值。
-
在会计年度字段中插入切片器。
-
在切片器中选择 FY2016。
预测措施和分析
您可以使用预测度量来分析财务数据并帮助组织对其年度目标和指标进行必要的调整，以使公司的绩效与不断变化的业务需求保持一致。
您需要定期更新预测以跟上变化。然后，您可以将最近的预测与财政年度剩余时间的预算进行比较，以便公司可以进行所需的调整以满足业务变化。
在财政年度的任何时候，您都可以计算以下内容 –
预测达到百分比
Forecast Attainment % 是您迄今为止花费的预测总和的百分比，即
预测达到百分比 = YTD 实际总和/YTD 预测总和
预测未用余额
预测未支出余额是实际支出后剩余的预测金额，即
预测未支出余额 = YTD 预测总和 – YTD 实际总和
预算调整
预算调整是组织需要根据预测进行（增加或减少）预算总额的调整。
预算调整 = 预测未用余额 – 未用余额
如果结果值为正，则需要增加预算。否则，可以出于其他目的对其进行调整。
创建预测实现百分比度量
您可以创建预测达到百分比措施如下 –
预测实现百分比：= IF([YTD 预测总和]，[YTD 实际总和]/[YTD 预测总和]，BLANK())
创建预测未支出余额度量
您可以创建预测未支出余额措施如下 –
预测未使用余额：=[YTD 预测总和]-[YTD 实际总和]
创建预算调整措施
您可以创建预算调整措施如下 –
预算调整：=[预测未用余额]-[未用余额]
使用预测测量分析数据
创建一个 Power PivotTable 如下 –
-
将日期表中的月份添加到行。
-
将财务数据表中的度量预算总和、YTD 预算总和、YTD 实际总和、预算实现百分比和未支出余额添加到值。
-
在会计年度插入切片器。
-
在切片器中选择 FY2016。
月数测量
您可以创建可用于创建 Headcount 度量和 Cost Per Head 度量的 Count of Months 度量。这些度量计算财政月列的不同值，其中实际列/预算列/预测列在财务数据表中具有非零值。这是必需的，因为财务数据表在实际列中包含零值，并且在计算人数和人均成本时将排除这些行。
创建实际月数度量
您可以按如下方式创建实际月数测量 –
CountOfActualMonths:=CALCULATE(DISTINCTCOUNT(‘FinanceData’ [财政月]),’Finance Data'[实际]<>0)
创建预算月数度量
您可以按如下方式创建预算月数测量 –
CountOfBudgetMonths:=CALCULATE(DISTINCTCOUNT(‘FinanceData’ [财政月]),’Finance Data'[Budget]<>0)
创建预测月数度量
您可以按如下方式创建预测月数测量 –
CountOfForecastMonths:=CALCULATE(DISTINCTCOUNT(‘FinanceData’ [财政月]),’Finance Data'[预测]<>0)
终止人数措施
您可以创建特定时间段的期末人数度量。Ending Headcount 是在指定时期内最后一个日期的人数总和，我们有非空的人数。
最终人数获得如下 –
-
For a Month – 特定月份结束时的人数。
-
For a Quarter – 特定季度最后一个月结束时的人数。
-
For a Year – 特定年份最后一个月结束时的总人数。
创建实际期末员工人数度量
您可以按如下方式创建实际结束人数指标 –
实际结束人数：=CALCULATE(SUM(‘财务数据'[实际人数]),LASTNONBLANK(‘财务数据'[日期], IF(CALCULATE(SUM(‘财务数据'[实际人数]),ALL(Accounts) )=0, BLANK(), CALCULATE(SUM(‘财务数据'[实际人数]), 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))
创建上一年实际期末员工人数度量
您可以按如下方式创建上一年度实际结束人数指标 –
上一年实际结束人数：=CALCULATE(‘财务数据'[实际结束人数], DATEADD(‘日期'[日期],-1,YEAR))
使用结束人数指标分析数据
创建一个 Power PivotTable 如下 –
-
将字段 Fiscal Year 和 Month 从 Date 表添加到 Rows。
-
将财务数据表中的度量实际结束人数、预算结束人数、预测结束人数、上一年实际结束人数添加到值。
-
在会计年度字段中插入切片器。
-
在切片器中选择 FY2016。
平均人数指标
在前一章中，您学习了如何计算特定时期的期末人数。同样，您可以为任何给定的月份选择创建平均每月员工人数。
平均每月员工人数是每月员工人数的总和除以选择的月数。
您可以使用 DAX AVERAGEX 函数创建这些度量。
创建实际平均人数衡量标准
您可以创建实际平均人数测量如下 –
实际平均人数：=AVERAGEX(VALUES(‘财务数据'[财政月])，[实际结束人数])
创建预算平均人数衡量标准
您可以创建实际平均人数测量如下 –
预算平均人数:=AVERAGEX(VALUES(‘财务数据'[财政月]),[预算结束人数])
创建预测平均人数测量
您可以创建预测平均人数测量如下 –
预测平均人数：=AVERAGEX( VALUES(‘财务数据'[财政月]), [实际结束人数])
创建上一年实际平均员工人数衡量标准
您可以按如下方式创建上一年实际平均员工人数指标 –
上一年实际平均人数：=CALCULATE(‘财务数据'[实际平均人数], DATEADD(‘日期'[日期], -1, YEAR))
使用平均人数指标分析数据
创建一个 Power PivotTable 如下 –
-
将字段 Fiscal Year 和 Month 从 Date 表添加到 Rows。
-
将财务数据表中的度量实际平均员工人数、预算平均员工人数、预测平均员工人数、上一年实际平均员工人数添加到值。
-
在会计年度字段中插入切片器。
-
在切片器中选择 FY2016。
总人数措施
在前面的章节中，您学习了如何创建 Count of Months 度量和 Average Headcount 度量。您可以使用这些度量来计算基本的人数度量 –
- 实际总人数
- 预算总人数
- 预测总人数
在后续章节中，您将学习如何在其他计算中使用这些基本员工人数度量，例如同比员工人数和方差度量。
创建实际总人数测量
您可以创建实际总人数测量如下 –
实际总人数：= ‘财务数据'[实际平均人数]*’财务数据'[CountOfActualMonths]
创建预算总人数度量
您可以创建预算总人数测量如下 –
预算总人数：= ‘财务数据'[预算平均人数]*’财务数据'[CountOfBudgetMonths]
创建预测总人数测量
您可以创建预测总人数测量如下 –
预测总人数：= ‘财务数据'[预测平均人数]*’财务数据'[CountOfForecastMonths]
同比员工人数衡量和分析
在前一章中，您已经学习了如何创建基本员工人数衡量标准——即实际总员工人数、预算总员工人数和预测总员工人数。
在本章中，您将学习如何创建同比员工人数度量以及如何使用这些度量分析数据。
创建同比实际期末员工人数衡量标准
您可以按如下方式创建年度实际结束人数衡量标准 –
同比实际期末人数：=[期末实际人数]-[上年实际期末人数]
创建同比实际平均员工人数衡量标准
您可以创建同比实际平均人数测量如下 –
同比实际平均人数：= [实际平均人数]-[上一年实际平均人数]
创建同比实际总人数衡量标准
您可以按如下方式创建同比实际总人数测量 –
同比实际总人数：=[实际总人数]-[上一年实际总人数]
使用年度实际员工人数指标分析数据
创建一个 Power PivotTable 如下 –
-
将“日期”表中的“财政季度”和“月份”字段添加到“行”。
-
将度量 – 实际结束人头数、上一年实际结束人头数、YoY 实际结束人头数添加到值。
-
在字段 Fiscal Year 上插入切片器。
-
在切片器中选择 FY2016。
在同一个工作表上创建另一个 Power PivotTable，如下所示 –
-
将“日期”表中的“财政季度”和“月份”字段添加到“行”。
-
将度量 – 实际平均人数、上一年实际平均人数、YoY 实际平均人数添加到值。
将切片器连接到此数据透视表，如下所示 –
- 单击切片器。
- 单击功能区上切片器工具下的选项选项卡。
- 单击报告连接。
出现报告连接对话框。
- 选择上面两个数据透视表。
- 单击确定。
创建年度预算期末员工人数衡量标准
您可以按如下方式创建年度预算期末人数指标 –
同比预算期末人数：= [预算期末人数]-[上年实际期末人数]
创建同比预算平均员工人数衡量标准
您可以按如下方式创建年度预算平均人数衡量标准 –
同比预算平均人数：= [预算平均人数]-[上一年实际平均人数]
创建年度预算总人数衡量标准
您可以按如下方式创建年度预算总人数测量 –
同比预算总人数：=[预算总人数]-[上一年实际总人数]
创建同比预测期末员工人数指标
您可以按如下方式创建同比预测结束人数指标 –
同比预测期末人数：= [预测期末人数]-[上年实际期末人数]
创建同比预测平均员工人数指标
您可以创建同比预测平均人数测量如下 –
同比预测平均人数：= [预测平均人数]-[上一年实际平均人数]
创建同比预测总人数指标
您可以创建同比预测总人数测量如下 –
同比预测总人数：=[预测总人数]-[上一年实际总人数]
差异人数测量
您可以根据目前已创建的人数度量来创建方差人数度量。
为预算期末员工人数度量创建差异
您可以按如下方式创建预算结束人数度量的差异 –
VTB期末人头数：= ‘财务数据'[预算期末人头数]-‘财务数据'[实际期末人头数]
为预算平均员工人数指标创建差异
您可以按如下方式创建预算平均人数测量的差异 –
VTB平均人数：= ‘财务数据'[预算平均人数]-‘财务数据'[实际平均人数
为预算总人数度量创建差异
您可以按如下方式创建预算总人数度量的差异 –
VTB 总人数：= ‘财务数据'[预算总人数]-‘财务数据'[实际总人数]
创建方差以预测期末员工人数度量
您可以创建方差以预测结束人数测量，如下所示 –
VTF Ending Head Count:= ‘Finance Data'[预测结束人头数]-‘Finance Data'[实际结束人头数]
创建方差以预测平均人数测量
您可以创建方差以预测平均人数测量，如下所示 –
VTF 平均人数：= ‘财务数据'[预测平均人数]-‘财务数据'[实际平均人数]
创建方差以预测总人数度量
您可以创建方差来预测总人数测量如下 –
VTF 总人数：= ‘财务数据'[预测总人数]-‘财务数据'[实际总人数]
创建预算期末员工人数度量的预测差异
您可以按如下方式创建预算结束人数度量的预测差异 –
Forecast VTB Ending Head Count:= ‘Finance Data'[Budget Ending Head Count]-‘Finance Data'[预测结束人头数]
为预算平均人数衡量指标创建预测差异
您可以按如下方式创建预算平均人数测量的预测差异 –
预测VTB平均人数：=’财务数据'[预算平均人数]-‘财务数据'[预测平均人数]
为预算总人数衡量创建预测差异
您可以按如下方式创建预算总人数度量的预测差异 –
预测 VTB 总人数：= ‘财务数据'[预算总人数]-‘财务数据'[预测总人数
人均成本衡量和分析
您已经了解了两大类措施 –
- 财务措施。
- 人数措施。
您将学习的第三大类度量是人员成本度量。任何组织都会有兴趣了解人均年化成本。人均年化成本代表公司在全年基础上拥有一名员工的成本。
要创建人均成本度量，您需要首先创建某些初步的人员成本度量。在 Accounts 表中，有一列 – Sub Class 包含 People 作为值之一。因此，您可以在“子类”列上的“帐户”表上应用过滤器，以获取财务数据表上的过滤器上下文，从而获取人员成本。
因此，您可以使用获取人员成本度量和月数度量来创建年度人员成本度量。您最终可以从年度人员成本度量和平均人头数度量中创建年度人均成本度量。
创建实际人员成本度量
您可以创建实际人员成本测量如下 –
实际人员成本：=CALCULATE(‘财务数据'[实际金额], FILTER(‘财务数据’, RELATED(Accounts[Sub Class])=”人”))
创建预算人员成本度量
您可以创建预算人员成本措施如下 –
预算人员成本:=CALCULATE(‘财务数据'[预算总和], FILTER(‘财务数据’, RELATED(Accounts[Sub Class])=”人”))
创建预测人员成本度量
您可以创建预测人员成本测量如下 –
预测人员成本:=CALCULATE(‘财务数据'[预测总和], FILTER(‘财务数据’, RELATED(Accounts[Sub Class])=”人”))
创建年度实际人员成本度量
您可以按如下方式创建年度实际人员成本度量 –
年化实际人员成本：=IF([CountOfActualMonths],[实际人员成本]*12/[CountOfActualMonths],BLANK())
创建年度预算人员成本度量
您可以按如下方式创建年度预算人员成本度量 –
年度预算人员成本：=IF([CountOfBudgetMonths], [Budget People Cost]*12/[CountOfBudgetMonths],BLANK())
创建年度预测人员成本度量
您可以按如下方式创建年度预测人员成本度量 –
年化预测人员成本：=IF([CountOfForecastMonths],[预测人员成本]*12/[CountOfForecastMonths],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 添加到列。
-
在字段 Fiscal Year from Date 表中插入切片器。
-
在切片器上选择 FY2015 和 FY2016。
利率差异和体积差异
您已经学习了如何为年度人均成本和总员工数创建度量。您可以使用这些度量来创建比率差异和体积差异度量。
-
汇率差异度量计算货币差异的哪一部分是由人均成本的差异引起的。
-
成交量差异度量计算货币差异有多少是由人数波动驱动的。
创建预算率度量的差异
您可以按如下方式创建预算率指标的差异 –
VTB率：=（[预算年度CPH]/12-[实际年度CPH]/12）*[实际总人数]
为预算量度量创建差异
您可以按如下方式创建预算量的差异 –
VTB 数量：=[VTB 总人数]*[预算年度 CPH]/12
分析具有预算度量差异的数据
创建一个 Power PivotTable 如下 –
- 将字段财政季度和财政月从日期表添加到行。
- 将度量实际年度 CPH、预算年度 CPH、VTB 率、VTB 量、VTB 总和添加到值。
- 将“日期”表中的“财政年度”字段和“帐户”表中的子类字段添加到过滤器。
- 在会计年度过滤器中选择 FY2016。
- 在子类过滤器中选择人员。
- 过滤 2016-Q1 和 2016-Q2 财政季度值的行标签。
您可以在上面的数据透视表中观察以下内容 –
-
显示的 VTB 总和值仅适用于子类 – 人员。
-
对于 2016-Q1 财政季度，VTB 金额为 4,705,568 美元，VTB 费率为 970,506,297 美元，VTB 数量为-965,800,727 美元。
-
VTB Rate 度量计算出预算差异 (VTB Sum) 中的 970,506,297 美元是由人均成本的差异引起的，而 $-965,800,727 是由人数差异引起的。
-
如果您添加 VTB Rate 和 VTB Volume，您将获得 4,705,568 美元，与 VTB Sum for Sub Class People 返回的值相同。
-
同样，对于 FY2016-Q2 财政季度，VTB 费率为 1,281,467,662 美元，VTB 交易量为 -1,210,710,978 美元。如果添加 VTB Rate 和 VTB Volume，您将获得 70,756,678 美元，这是数据透视表中显示的 VTB 总和值。
创建同比率度量
您可以按如下方式创建同比率度量 –
同比率：=（[实际年化 CPH]/12-[上年实际年化 CPH]/12）*[实际总人数]
创建同比数量测量
您可以按如下方式创建同比数量测量 –
同比销量：=[YoY 实际总人数]*[上年实际年度 CPH]/12
为预测率度量创建方差
您可以按如下方式创建预测率度量的方差 –
VTF率：=（[预测年化CPH]/12-[实际年化CPH]/12）*[实际总人数]
创建方差以预测体积测量
您可以创建方差来预测体积度量，如下所示 –
VTF 量：=[VTF 总人数]*[预测年度 CPH]/12
分析数据与预测措施的差异
创建一个 Power PivotTable 如下 –
-
将字段财政季度和财政月从日期表添加到行。
-
将测量实际年化 CPH、预测年化 CPH、VTF 率、VTF 量、VTF 总和添加到值。
-
将“日期”表中的“财政年度”字段和“帐户”表中的子类字段添加到过滤器。
-
在会计年度过滤器中选择 FY2016。
-
在子类过滤器中选择人员。
-
过滤 2016-Q1 和 2016-Q2 财政季度值的行标签。
为预算率度量创建预测差异
您可以按如下方式创建预算率指标的预测差异 –
预测 VTB 费率:=([预算年度 CPH]/12-[预测年度 CPH]/12)*[预测总人数]
为预算量度量创建预测差异
您可以按如下方式创建预算量指标的预测差异 –
预测 VTB 数量：=[预测 VTB 总人数]*[预算年度 CPH]/12
使用预算度量的预测差异分析数据
创建一个 Power PivotTable 如下 –
-
将字段财政季度和财政月从日期表添加到行。
-
将预算年度 CPH、预测年度 CPH、预测 VTB 率、预测 VTB 量、预测 VTB 总和这些措施添加到值中。
-
将“日期”表中的“财政年度”字段和“帐户”表中的子类字段添加到过滤器。
-
在会计年度过滤器中选择 FY2016。
-
在子类过滤器中选择人员。
-
过滤 2016-Q1 和 2016-Q2 财政季度值的行标签。
