Data Modeling With Dax 简明教程
Data Modeling with DAX - Overview
所有组织的决策者都已明确指出，需要分析组织的历史数据，特别是整个行业的历史数据。在当今竞争激烈的世界中，为了应对不断变化的业务挑战，这一点正变得越来越重要。
大数据和商业智能已成为商业领域的热门词汇。数据源变得巨大，数据格式也变得多样化。当务之急是拥有易于使用的工具，以便在更短的时间内处理不断流动的庞大数据，从而深入了解并在适当的时间做出相关决策。
数据分析师不再需要等待 IT 部门处理所需数据。他们需要一个方便的工具，使他们能够快速理解所需数据，并将其提供以一种有助于决策者在正确时间采取必要行动的格式。
Microsoft Excel 拥有一个功能强大的工具，称为 Power Pivot，在 Excel 的早期版本中可用作加载项，并且在 Excel 2016 中已成为内置功能。Power Pivot 数据库（称为数据模型）以及作用于数据模型的公式语言（称为 DAX，即 *D*ata *A*nalysis *E*xpressions，数据分析表达式）使 Excel 用户能够在短时间内执行诸如数据建模和分析等任务。
在本教程中，您将基于 Power Pivot 数据模型来学习使用 DAX 进行数据建模和分析。本教程的说明中使用了一个利润和分析数据库样本。
Data Modeling with DAX - Concepts
商业智能 (BI) 在多个领域和组织中日益重要。在日益激烈的竞争环境中，基于历史数据的决策和预测已变得至关重要。对于任何类型的数据分析而言，都有大量数据可从内部和外部的不同来源获得。
然而，挑战在于根据当前需求从可用大数据中提取相关数据，并以一种便于从数据中预测不同见解的方式存储数据。因此，使用关键业务术语获得的数据模型是一种有价值的沟通工具。该数据模型还需要提供按需生成报告的快速方法。
面向 BI 系统的数据建模使您能够应对许多数据挑战。
Prerequisites for a Data Model for BI
BI 数据模型应满足正在对其进行数据分析的业务需求。以下是对任何数据模型提出的基本要求：
The data model needs to be Business Specific
适合一条业务线的模型可能不适合另一条业务线。因此，必须根据特定业务、使用的业务术语、数据类型及其关系来开发数据模型。它应基于组织的目标和决策类型。
Data Modeling for BI
BI 的数据建模包含以下步骤 -
-
Shaping the data
-
Loading the data
-
定义表之间的关系
-
Defining data types
-
Creating new data insights
Shaping the Data
构建数据模型所需的数据可以来自多种来源，并且可以采用不同的格式。您需要确定来自每个数据源的数据的哪一部分对于特定的数据分析是必需的。这被称为整形数据。
例如，如果您正在检索组织中所有员工的数据，则需要决定每个员工的哪些详细信息与当前上下文相关。换句话说，您需要确定需要导入员工表中的哪些列。这是因为，数据模型中表中的列数越少，对该表的计算速度就越快。
Data Modeling Using Excel Power Pivot
Microsoft Excel Power Pivot 是一个用于数据建模和分析的出色工具。
-
数据模型是 Power Pivot 数据库。
-
DAX 是可以使用 DAX 公式通过数据模型中的数据创建元数据的公式语言。
-
使用数据模型中的数据和元数据创建的 Excel 中的 Power 透视表使您能够分析数据并呈现结果。
在本教程中，您将学习使用 Power Pivot 数据模型和 DAX 进行数据建模以及使用 Power Pivot 进行数据分析。如果您不熟悉 Power Pivot，请参阅 Excel Power Pivot 教程。
您已在上一章 - 数据建模和分析概念 - 中学习数据建模过程步骤。在本章中，您将学习如何使用 Power Pivot 数据模型和 DAX 执行每个这些步骤。
在以下各节中，您将学习应用于 Power Pivot 数据模型的每个过程步骤，以及如何使用 DAX。
Shaping the Data
在 Excel Power Pivot 中，您可以从各种类型的数据源导入数据，并在导入时，您可以查看并选择要导入的表和列。
-
Identify the data sources.
-
查找数据源类型。例如，数据库或数据服务或任何其他数据源。
-
决定在当前上下文中有哪些数据相关。
-
为数据决定适当的数据类型。在 Power Pivot 数据模型中，在一张表中的整个列中只能使用一种数据类型。
-
识别哪张表为事实表，哪张表为维度表。
-
决定表之间相关的逻辑关系。
Loading Data into the Data Model
您可以通过功能区“Power Pivot”窗口中提供的多个选项将数据加载到数据模型中。你可以在该组中找到这些选项，“获取外部数据”。
在章 – 将数据加载到数据模型中，您将学习如何从 Access 数据库加载数据到数据模型。
出于说明目的，已使用一个包含损益数据的 Access 数据库。
Defining Data Types in the Data Model
Power Pivot 中数据建模过程中的下一步是定义加载到数据模型中的表的列的数据类型。
在章 – 在数据模型中定义数据模型中，您将学习如何定义表中的列的数据类型。
Creating Relationships Between the Tables
Power Pivot 中数据建模过程中的下一步是创建数据模型中的表之间的关系。
在章 – 扩展数据模型中，您将学习如何创建表之间的关系。
Creating New Data Insights
在数据模型中，您可以通过下列方法创建生成新的数据见解的元数据 −
-
Creating Calculated Columns
-
Creating Date Table
-
Creating Measures
然后可以通过创建基于表中的列和作为数据透视表字段列表中字段的衡量指标，来分析数据。
Creating Date Table
为了使用 DAX 公式中的时间智能函数来创建元数据，您需要一个日期表。如果您不熟悉日期表，请参阅章– 理解日期表。
在章 – 扩展数据模型中，您将学习如何在数据模型中创建日期表。
Loading Data into the Data Model
你可以从不同类型的数据源将数据加载到数据模型中。为此，你可以在Power Pivot窗口的Ribbon（功能区）中的获取外部数据组中找到各种选项。
正如你所观察到的那样，你可以从数据库中或从数据服务或其他几种类型的数据源中加载数据。
当你从数据源中将数据加载到数据模型中时，将会与数据源建立连接。这使得当源数据更改时能够刷新数据。
Initiating with a New Data Model
在本节中，你将学习如何为利润和损益分析建模数据。分析数据在一个Microsoft Access数据库中。
你可以如下启动一个新的数据模型−
-
打开一个新的Excel工作表
-
单击Ribbon（功能区）上的PowerPivot选项卡
-
单击数据模型组中的管理
此时将出现Power Pivot窗口。由于你尚未加载任何数据，所以该窗口将为空。
Loading Data from Access Database into the Data Model
要从Access数据库加载数据，请执行以下步骤−
-
单击Ribbon（功能区）上的获取外部数据组中的来自数据库。
-
在下拉列表中单击来自Access。
表导入向导对话框出现。
-
浏览到Access文件。
-
为连接提供一个友好名称。
-
单击下一步按钮。表的导入向导的下一部分出现。
-
在“表导入向导”中，选择选项 – 从表和视图列表中选择要导入的数据。
-
单击“下一步”按钮，表导入向导的下一部分会按以下屏幕截图所示的方式显示。
-
Select all the tables.
-
将友好名称提供给表。这很重要，因为这些名称会显示在 PowerPivot 表中，因此每个人都应该理解。
Choosing the Columns in the Tables
对于当前分析，你可能不需要选定表中的所有列。因此，你需要仅选择在构建数据时选择的那些列。
-
单击“预览和筛选”按钮，表导入向导的下一部分 - 所选表的预览 - 显示。
-
正如在上一个屏幕截图中所示，列头有复选框。在所选表中选择要导入的列。
-
单击“确定”。对其他表重复相同的步骤。
Importing Data into the Data Model
你现在正处于将数据加载到数据模型的最后阶段。在表导入向导中单击“完成”按钮，表导入向导的下一部分显示。
导入状态将显示。当数据加载完成时，状态最终会显示为“成功”。
Defining Data Types in the Data Model
在 PowerPivot 数据模型中，一列中的所有数据必须具有相同数据类型。为了执行准确计算，你需要确保数据模型中每张表中的每列的数据类型符合需求。
Ensuring Appropriate Data Types
为了确保表中列符合需求，你需要在 PowerPivot 窗口中检查其数据类型。
-
单击表中的某一列。
-
请注意功能区中“格式”组中显示的列的数据类型。
如果所选列的数据类型不合适，请如下更改数据类型。
-
单击“格式”组中数据类型旁边的向下箭头。
-
在下拉列表中点击相应的数据类型。
-
对于数据模型中所有表的所有列重复执行重复。
Columns in the Accounts Table
在 Accounts 表中，您有以下的列 −
Sr.No |
Column & Description |
1 |
*Account*对于每一行包含一个帐户编号。该列具有唯一的值，用于定义与财务数据表的关联。 |
2 |
*Class*与每个帐户关联的类别。例如 - 费用、净收入等。 |
3 |
*Sub Class*描述费用的类型或收入。例如 - 人员。 |
Accounts 表中的所有列都具有描述的性质，因此是 Text 数据类型。
Columns in the Geography Locn Table
Geography Locn 表包含每个利润中心的数据。
Profit Center 列对于每一行包含一个利润中心标识。此列具有唯一的值，用于定义与财务数据表的关联。
Columns in the Finance Data Table
在财务数据表中，您有以下列 −
Column |
Description |
Data type |
Fiscal Month |
Month and Year |
Text |
Profit Center |
Profit Center identity |
Text |
Account |
Account number.每个帐户可以有多个利润中心。 |
Text |
Budget |
对于每个利润中心，按月编制的预算额度。 |
Currency |
Actual |
对于每个利润中心，按月编制的实际额度。 |
Currency |
Forecast |
每月利润中心预测总额。 |
Currency |
Actual People |
按人员帐户的利润中心月底实际员工数。 |
Whole Number |
Budget People |
按人员帐户的利润中心月底预算员工数。 |
Whole Number |
Forecast People |
根据每位员工的每个利润中心的帐号每个月的预测人数。 |
Whole Number |
Understanding Data Tables
数据分析涉及浏览一段时间的数据并计算各个时间段的数据。例如，您可能必须将今年的利润与去年的利润进行比较。同样，您可能必须预测未来几年的增长和利润。为此，您需要对一段时间内的数据进行分组和聚合。
DAX 提供了几个时间智能函数，可帮助您执行大多数此类计算。但是，这些 DAX 函数需要一个“日期”表才能与数据模型中的其他表一起使用。
您可以从数据源导入“日期”表以及其他数据，也可以自己在数据模型中创建“日期”表。
在本章中，您将了解“日期”表的不同方面。如果您熟悉 Power Pivot 数据模型中的“日期”表，您可以跳过本章并继续学习后续章节。否则，您可以了解 Power Pivot 数据模型中的“日期”表。
What is a Date Table?
“日期”表是数据模型中的一张表，至少有一列连续日期跨越所需持续时间。它可以有其他表示不同时间段的列。然而，正如 DAX 时间智能函数所需的，连续的日期列是必需的。
例如，
-
“日期”表可以包含日期、财政月份、财政季度和财政年份等列。
-
“日期”表可以包含日期、月份、季度和年份等列。
Date Table with Contiguous Dates
假设您需要在公历年范围内进行计算。那么，“日期”表至少必须有一列包含连续日期集，包括该特定公历年中的所有日期。
例如，假设您要浏览的数据的日期从 2014 年 4 月 1 日持续到 2016 年 11 月 30 日。
-
如果您必须报告公历年，您需要一张“日期”表，其中包含一列 - “日期”，其按顺序包含从 2014 年 1 月 1 日到 2016 年 12 月 31 日的所有日期。
-
如果您必须报告财政年度，并且您的财政年度末是 6 月 30 日，您需要一张“日期”表，其中包含一列 - “日期”，其按顺序包含从 2013 年 7 月 1 日到 2017 年 6 月 30 日的所有日期。
-
如果您必须报告公历年和财政年度，那么您可以拥有一张涵盖所需日期范围的“日期”表。
表中必须包含给定期限内每一年所有日期。因此，你会在这段时间内得到连续的日期。
如果你会定期用新数据刷新数据，那么你必须将结束日期延长一年或两年，这样你就不必频繁更新表。
表看起来像下面的屏幕截图。
Adding a Date Table to the Data Model
你可以通过以下方法将表添加到数据模型中——
-
从关系数据库或任何其他数据源导入。
-
在 Excel 中创建一个表，然后复制或链接到 Power Pivot 中的一个新表。
-
从 Microsoft Azure Marketplace 导入。
Creating a Date Table in Excel and Copying to the Data Model
在 Excel 中创建一个表并复制到数据模型是最简单、最灵活的数据模型创建方法。
-
在 Excel 中打开一个新工作表。
-
- 在列第一行输入日期。
-
在同一列第二行中输入要创建的日期范围内的第一日期。
-
选择单元格，单击填充柄并将其向下拖动，在所需日期范围内创建连续日期的列。
例如，输入 1/1/2014，单击填充柄并向下拖动填充连续日期，直至 31/12/2016。
-
Click the Date column.
-
单击功能区上的插入选项卡。
-
Click Table.
-
Verify the table range.
-
Click OK.
Excel 中准备好了单列日期的表。
-
Select the table.
-
单击功能区上的复制。
-
单击 Power Pivot 窗口。
-
单击功能区上的粘贴。
这将把剪贴板内容添加到数据模型中的新表。因此，你也可以使用同样的方法在现有数据模型中创建表。
在下面的屏幕截图所示的粘贴预览对话框中显示。
-
在表名框中键入日期。
-
Preview the data.
-
选中框 - 将第一行用作列标题。
-
Click OK.
这会将剪贴板内容复制到数据模型中的新表中。
现在，你在数据模型中有了一个日期表，其中包含一列连续日期。如你在 Excel 表中给出的那样，该列的标题是日期。
Adding New Date Columns to the Date Table
接下来，你可以根据计算需求向日期表中添加计算列。
例如，你可以按如下方式添加列 - 日、月、年和季度 −
-
Day =DAY('Date'[Date])
-
Month =MONTH('Date'[Date])
-
Year =YEAR('Date'[Date])
-
季度 = CONCATENATE("QTR", INT 'Date'[Month]+2)/3
数据模型中的结果日期表如下面的屏幕截图所示。
因此，你可以向日期表中添加任意数量的计算列。重要的是，并且需要的是，日期表必须有一列连续日期，跨越你要执行计算的时间范围。
Creating a Date Table for a Calendar Year
一个日历年通常包括一年中 1 月 1 日至 12 月 31 日的日期，还包括为该特定年份标记的节假日。当你执行计算时，你可能只考虑工作日，不包括周末和节假日。
假设你想为日历年 2017 创建一个日期表。
-
创建一个 Excel 表，其中有一列日期，包括从 2017 年 1 月 1 日到 2017 年 12 月 31 日的连续日期。（请参考上一部分了解如何执行此操作。）
-
复制 Excel 表并将其粘贴到数据模型中的新表中。（请参考上一部分了解如何执行此操作。）
-
将该表的名称命名为日历。
-
添加以下计算列 - 日 = DAY('Calendar'[Date]) 月 = MONTH('Calendar'[Date]) 年 = YEAR('Calendar'[Date]) 星期几 = FORMAT('Calendar'[Date],"DDD") 月份名称 = FORMAT('Calendar'[Date],"MMM")
Adding Holidays to the Calendar Table
按如下方式向日历表添加节假日 −
-
获取该年的申报节假日清单。
-
例如，对于美国，你可以从以下链接中获取任何需要的年份的节假日清单 http://www.calendar-365.com/ 。
-
将其复制粘贴到 Excel 工作表中。
-
复制 Excel 表格并将其粘贴到数据模型中的新表格中。
-
将该表命名为 Holidays。
-
接下来，可以使用 DAX LOOKUPVALUE 函数，将假日计算列添加到日历表中。
=LOOKUPVALUE(Holidays[Holiday],Holidays[Date],'Calendar'[Date])
DAX LOOKUPVALUE 函数在第三个参数（即 Holidays[日期] 中的日历[日期]）中搜索第二个参数（即 Holidays[日期]），如果存在匹配项，则返回第一个参数（即 Holidays[假日]）。结果将类似于以下屏幕截图中显示的内容。
Adding Columns to a Fiscal Year
一个财政年度通常包括从财政年度结束后的下一个月 1 日到下一个财政年度结束的日期。例如，如果财政年度结束日期是 3 月 31 日，则该财政年度是从 4 月 1 日到 3 月 31 日。
可以使用 DAX 公式将财政期间纳入日历表 −
-
添加 FYE 的措施 FYE:=3
-
添加以下计算列 − Fiscal Year =IF('Calendar'[月]⇐ 'Calendar'[FYE],'Calendar'[年],'Calendar'[role="Year"][.年]1) Fiscal Month =IF('Calendar'[月]⇐'Calendar'[FYE],12-'Calendar'[FYE]'Calendar'[月],'Calendar'[月]-'Calendar'[FYE] ) Fiscal Quarter =INT(('Calendar'[财政月]+2)/3)
Extending the Data Model
在本章中，您将学习如何扩展在前面的章节中创建的数据模型。扩展数据模型包括：
-
Addition of tables
-
在现有表中添加计算列
-
在现有表中创建度量
其中，创建度量至关重要，因为它涉及在数据模型中提供新的数据见解，这些见解将使用户在分析数据和做出决策时可以避免返工并节省时间。
由于损益分析涉及使用时间段并且您将使用 DAX 时间智能函数，因此您需要在数据模型中拥有“日期”表。
如果您不了解“日期”表，请通读章节——理解“日期”表。
您可以按以下方式扩展数据模型 -
-
为了在数据表（即“财务数据”表）与“日期”表之间创建关系，您需要在“财务数据”表中创建计算列“日期”。
-
为了执行不同类型的计算，您需要在数据表 - “财务数据”和查找表 - “帐户”和“地理位置”之间创建关系。
-
您需要创建各种度量，这些度量可帮助您执行多个计算并进行所需的分析。
这些步骤实质上构成了使用数据模型进行损益分析的数据建模步骤。但是，这是对您要使用 Power Pivot 数据模型执行的任何类型数据分析执行的一系列步骤。
此外，您还将在后续章节中了解如何创建度量以及如何在 Power 透视表中使用它们。这将让您充分了解使用 DAX 建模数据和使用 Power 透视表对数据进行分析的情况。
Adding a Date Table to the Data Model
为跨越财政年度的时间段创建“日期”表，如下所示 -
-
在新 Excel 工作表中创建一个单列表格，其中标题为 - “日期”，连续日期范围从 7/1/2011 到 6/30/2018。
-
从 Excel 中复制表格并将其粘贴到 Power Pivot 窗口中。这将在 Power Pivot 数据模型中创建一个新表格。
-
将该表命名为“日期”。
-
确保“日期”表中的“日期”列的数据类型为 - 日期 (DateTime)。
接下来，您需要根据以下步骤向“日期”表中添加计算列——财政年度、财政季度、财政月份和月份 -
Fiscal Year
假设财政年度末为 6 月 30 日。那么，一个财政年度从 7 月 1 日跨度到 6 月 30 日。例如，时期 7/1/2011 (7/1/2011) 至 6/30/2012 (6/30/2012) 将是财政年度 2012 年。
在“日期”表中，假设您想表示与 FY2012 相同的内容。
-
你需要首先提取日期的财政年度部分，并用 FY 附加。对于 2011 年 7 月到 2011 年 12 月的日期，财政年度为 1+2011。对于 2012 年 1 月到 2012 年 6 月的日期，财政年度为 0+2012。概括地说，如果财政年度结束的月份为 FYE，请执行以下操作 - Integer Part of ((Month – 1)/FYE) + Year 接下来，获取最右边的 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) 在 Date 表中添加计算的列 Fiscal Year
Fiscal Quarter
如果 FYE 表示财政年度结束的月份，则财政季度可获得为
Integer Part of ((Remainder of ((Month+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") 在 Date 表中添加计算的列 Fiscal Quarter
Adding Calculated Columns
要创建财务数据表和日期表之间的关系，需要在财务数据表中创建一列日期值。
-
在财务数据表中添加计算的列日期，使用 DAX 公式 −= DATEVALUE ('Finance Data'[Fiscal Month])
Defining Relationships Between Tables in the Data Model
在数据模型中有以下表 −
-
数据表 - 财务数据
-
查找表 - 帐户和地理位置
-
Date table - Date
若要在数据模型中的表格之间定义关系，请执行以下步骤：
-
查看 Power Pivot 中的“图表视图”中的表格。
-
创建表格之间的下列关系：财务数据表格与帐户表格间的关系，其中包含帐户列。财务数据表格与地理位置表格间的关系，其中包含利润中心列。财务数据表格与日期表格间的关系，其中包含日期列。
Base Finance Measures and Analysis
你可以在数据模型中创建各种度量，用于任意数量的 Power 透视表中。这构成了 DAX 使用数据模型的数据建模和分析过程。
正如你在前几小节中了解到的，数据建模和分析取决于特定的业务和上下文。在本小节中，你将根据利润和亏损示例数据库学习数据建模和分析，以便了解如何创建所需的度量，并在各种 Power 透视表中使用它们。
你可以针对任何业务和上下文使用相同的方法进行数据建模和分析
Creating Measures Based on Finance Data
要创建任何财务报表，你需要针对特定时间段、组织、帐户或地理位置计算金额。你还要执行人员计数和每人成本计算。在数据模型中，你可以创建基准衡量，以便在创建其他基准衡量时重复使用。这是使用 DAX 进行数据建模的有效方法。
为了执行利润和亏损数据分析计算，你可以创建总和、同比、年初至今、季初至今、差异、人员数量、每人成本等基准衡量。你可以在 Power 透视表中使用这些基准衡量来分析数据并报告分析结果。
在以下小节中，你将学习如何创建基础财务衡量并使用这些衡量分析数据。这些衡量被称为基础衡量，因为它们可用于创建其他财务衡量。你还会学习如何创建前一时间段的基准衡量并在分析中使用它们。
Creating Base Finance Measures
在财务数据分析中，预算和预测起着重要作用。
Budget
预算是对企业经济年度收益和支出的估算值。企业在经济年度初制定预算，还需要参照企业目标设定。需要在经济年度期间时不时分析预算指标，因为市场状况可能发生变化，企业可能需要调整其目标以适应行业中的当前趋势。
Forecast
财务预测是通过检查企业过去收益和支出的历史数据，估算企业未来的财务状况。你可以将财务预测用于以下方面：
-
决定如何为未来期间分配预算
-
追踪企业预期的表现
-
及时做出决策，解决相对于目标的不足之处，或最大化新出现的商机
Actuals
若要执行预算和预测计算，你需要任何时间点的实际收益和支出。
你可以创建以下 3 项财务基础指标，可以在数据模式中用其创建其他财务指标：
-
Budget Sum
-
Actual Sum
-
Forecast Sum
这些指标是在财务数据表中针对“预算”、“实际”和“预测”列的聚合总和。
按如下方式创建财务基础指标：
Budget Sum
Budget Sum:=SUM('Finance Data'[Budget])
Actual Sum
Actual Sum:=SUM('Finance Data'[Actual])
Forecast Sum
Forecast Sum:=SUM('Finance Data'[Forecast])
Analyzing Data with Base Finance Measures
借助财务基础指标和日期表，你可以按如下方式执行分析：
-
Create a Power PivotTable.
-
将日期表中的“会计年度”字段添加到行中。
-
将指标“Budget Sum”、“Actual Sum”和“Forecast Sum”（在数据透视表字段列表中显示为字段）添加到值中。
YoY Finance Measures and Analysis
同比（YoY）是度量增长。它通过从实际总额中减去前一年的实际总额来获得。
如果结果为正，则表示实际金额增加，如果结果为负，则表示实际金额减少，即如果我们将同比计算为：
year-over-year = (actual sum –prior year actual sum)
-
如果实际金额>前一年的实际金额，则同比将为正。
-
如果实际金额<前一年的实际金额，则同比将为负。
在财务数据中，诸如费用帐户之类的帐户将具有借方（正）金额，而收入帐户将具有贷方（负）金额。因此，对于费用帐户，上述公式完全适用。
但是，对于收入帐户，应相反，即：
-
如果实际金额>前一年的实际金额，则同比应为负。
-
如果实际总和 < 上一年实际总和，同比应为正值。
因此，对于收入账户，您需要以以下方式计算同比：
year-over-year = -(actual sum – prior year actual sum)
Creating Year-over-Year Measure
您可以使用以下 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。
-
然后，DAX IF 函数返回 –([Actual Sum] - [Prior Year Actual Sum])。
-
否则，DAX IF 函数返回 [Actual Sum] - [Prior Year Actual Sum]。
Creating Year-over-Year Percentage Measure
您可以使用比率将同比表示为百分比：
(YoY) / (Prior Year Actual Sum)
您可以使用以下 DAX 公式创建同比百分比度量：
YoY %:=IF([Prior Year Actual Sum], [YoY] / ABS([Prior Year Actual Sum]),BLANK())
上述公式中使用了 DAX IF 函数以确保不进行零除。
Analyzing Data with Year-over-Year Measures
按以下步骤创建 Power Pivot 透视表 −
-
从“账户”表中将字段类别和子类别添加到行。
-
将度量 - 实际总和、上一年实际总和、同比和同比百分比添加到数值。
-
在来自“日期”表的“会计年度”字段中插入切片器。
-
在切片器中选择“2016 财政年度”。
Creating Budget Year-over-Year Measure
您可以按以下方式创建预算同比度量：
预算同比：= IF(CONTAINS(Accounts,Accounts[Class],"Net Revenue"), - ([Budget Sum] - [Prior Year Actual Sum]), [Budget Sum] - [Prior Year Actual Sum])
Creating Budget Year-over-Year Percentage Measure
您可以按照以下方式创建预算同比百分比指标：
预算同比百分比:=IF([上年实际总计],[预算同比]/ABS([上年实际总计]),BLANK())
Analyzing Data with Budget Year-over-Year Measures
按以下步骤创建 Power Pivot 透视表 −
-
从“账户”表中将字段类别和子类别添加到行。
-
将指标添加到值中——预算总计、上年实际总计、预算同比和预算同比百分比。
-
在来自“日期”表的“会计年度”字段中插入切片器。
-
在切片器中选择“2016 财政年度”。
Creating Forecast Year-over-Year Measure
您可以按照以下方式创建预测同比指标：
预测同比:=IF(CONTAINS(科目,科目[分类],“净利润”), - ([预测总计] - [上年实际总计]),[预测总计] - [上年实际总计])
Variance Measures and Analysis
您可以创建诸如预算差异、预测差异和变动预算之差异之类的差异度量。您还可以根据这些度量分析财务数据。
Creating Variance to Budget Percentage Measure
按如下方式创建预算差异百分比度量（VTB %）：
VTB %：=IF([预算总和]，[VTB 总和]/ABS([预算总和])，BLANK())
Analyzing Data with Variance to Budget Measures
按以下步骤创建 Power Pivot 透视表 −
-
从日期表中将会计年度添加到行中。
-
将实际金额总和、预算金额总和、实际到预算金额总和、实际到预算百分比度量从财务数据表中添加到值中。
Year-to-Date Measures and Analysis
若要计算从一个时期（例如一个财政年度）开始到一个特定时间为止的包含期初余额的结果，可使用 DAX 时间智能函数。这将使您能够分析月度数据。
在本章中，您将学习如何创建“年初至今”度量，并使用它执行数据分析。
Creating Year-to-Date Actual Sum Measure
按以下方式创建“年初至今实际总和”度量：
YTD 实际总和：= TOTALYTD（[实际总和]，'日期'[日期]，ALL('日期')，"6/30"）
Creating Year-to-Date Budget Sum Measure
按以下方式创建“年初至今预算总和”度量：
YTD 预算总和：= TOTALYTD（[预算总和]，'日期'[日期]，ALL('日期')，"6/30"）
Creating Year-to-Date Forecast Sum Measure
按以下方式创建“年初至今预测总和”度量：
YTD 预测总和：= TOTALYTD（[预测总和]，'日期'[日期]，ALL('日期')，"6/30"）
Creating Prior Year-to-Date Actual Sum Measure
按以下方式创建“上一年年初至今实际总和”度量：
去年年初至今实际总和：= TOTALYTD（[去年实际总和]，'日期'[日期]，ALL('日期')，"6/30"）
Analyzing Data with Year-to-Date Measures
按以下步骤创建 Power Pivot 透视表 −
-
将“日期”表中的“月份”添加到行。
-
从“财务数据”表添加“实际总和”、“年初至今实际总和”、“年初至今预算总和”和“年初至今预测总和”度量到“值”。
-
从“日期”表添加“财政年度”切片器。
-
在切片器中选择“2016 财政年度”。
按以下步骤创建 Power Pivot 透视表 −
-
将“日期”表中的“月份”添加到行。
-
从“财务数据”表添加“实际总和”、“年初至今实际总和”、“去年实际总和”和“去年年初至今实际总和”度量到“值”。
-
从“日期”表添加“财政年度”切片器。
-
在切片器中选择“2016 财政年度”。
Quarter-to-Date Measures and Analysis
若要计算包括一段时间的起始余额（例如会计季度）直到特定时间的某个结果，您可以使用 DAX 时间智能函数。这使您能够分析按月汇总的数据。
在本章中，您将学习如何创建年初至今度量以及如何使用相同的度量执行数据分析。
Creating Quarter-to-Date Sum Measure
按照以下步骤创建年初至今实际总和度量：
年初至今实际总和：=TOTALQTD([实际总和，'日期'[日期]，ALL('日期')])
Creating Quarter-to-Date Budget Sum Measure
按照以下步骤创建年初至今预算总和度量：
年初至今预算总和：=TOTALQTD([预算总和]，'日期'[日期]，ALL('日期')])
Creating Quarter-to-Date Forecast Sum Measure
按照以下步骤创建年初至今预算总和度量：
年初至今预算总和：=TOTALQTD([预算总和]，'日期'[日期]，ALL('日期')])
Creating Quarter-to-Date Forecast Sum Measure
按照以下步骤创建年初至今预测总和度量：
年初至今预测总和：=TOTALQTD([预测总和]，'日期'[日期]，ALL('日期')])
Creating Prior Quarter-to-Date Actual Sum Measure
按照以下步骤创建前年初至今实际总和度量：
前年初至今实际总和：=TOTALQTD([上前一个季度实际总和]，'日期'[日期]，ALL('日期')])
Analyzing Data with Quarter-to-Date Measures
按以下步骤创建 Power Pivot 透视表 −
-
从“日期”表将“会计月”添加到“行”。
-
从“财务数据”表将度量“实际总和”、“年初至今实际总和”、“年初至今预算总和”和“年初至今预测总和”添加到“值”。
-
从“日期”表插入“会计季度”切片器。
-
在切片器中选择“2016 财年 - 第 2 季度”。
按以下步骤创建 Power Pivot 透视表 −
-
从“日期”表将“会计月”添加到“行”。
-
从财务数据表中将实际总和、季度实际总和、上季度实际总和和上季度实际总和措施添加到“值”。
-
在日期表中插入“财政季度”切片器。
-
在切片器中选择 FY2016-Q1。
Budget Measures and Analysis
预算涉及估算公司的现金流在某一财务年度的变化情况。公司的财务状况、其目标、预期收入和支出都会计算在预算中。
但是，市场状况可能会在财务年度发生变化，公司可能必须重新设定其目标。这就需要分析财务数据与在财务年度开始时估算的预算（预算总和）和从财务年度开始至今的实际支出总和（YTD 实际总和）。
在财政年度的任何时间，都可以计算出以下内容 -
Budget Attainment %
预算达成率百分比是您至今为止已支出预算的百分比，即
Budget Attainment % = YTD Actual Sum/YTD Budget Sum
这些计算可帮助那些使用预算的公司做出决策。
Creating Unexpended Balance Measure
您可以按如下方式创建未支出余额计量：
未支出余额：=CALCULATE( [YTD 实际总和]，ALL('财务数据'[日期]) )-[YTD 实际总和]
Forecast Measures and Analysis
您可以使用预测度量来分析财务数据，并帮助组织对其年度目标进行必要的调整，以使公司的业绩符合不断变化的业务需求。
您需要定期更新预测以跟上变化。然后可以将最新的预测与本财政年度剩余期间的预算进行比较，以便公司可以根据业务变化进行必要的调整。
在财政年度的任何时间，都可以计算出以下内容 -
Forecast Attainment %
预测达到率 % 是您迄今为止花费的预测总和的百分比，即
Forecast Attainment % = YTD Actual Sum/YTD Forecast Sum
Forecast Unexpended Balance
预测未支余额是实际支出后的预测总和，即
Forecast Unexpended Balance = YTD Forecast Sum – YTD Actual Sum
Budget Adjustment
预算调整是组织根据预测需要进行的预算总和调整（增加或减少）。
Budget Adjustment = Forecast Unexpended Balance - Unexpended Balance
如果所得的值为正，则需要增加预算。否则，可以针对其他目的进行调整。
Count of Months Measures
您可以创建可用于创建人数度量和人均成本度量的月数计数度量。这些度量计算财务数据表中实际列/预算列/预测列具有非零值的财政月列的不同值的计数。这是必需的，因为财务数据表中实际列包含零值，在计算人数和人均成本时应排除这些行。
Creating Count of Actual Months Measure
您可以按如下方式创建实际月数计数度量 -
实际月数计数 = CALCULATE(DISTINCTCOUNT('FinanceData' [财政月]),'Finance Data'[实际] <> 0)
Ending Headcount Measures
可以为特定时间段创建结束人数措施。结束人数是指定期间的最后日期的人数总和，对于该日期，我们具有非零人数总和。
结束人数获取方式如下 −
-
对于一个月 − 该特定月底的人数总和。
-
对于季度 - 指定季度的最后月份结束时的总人数。
-
对于年份 - 指定年份的最后月份结束时的总人数。
Creating Actual Ending Headcount Measure
您可以按如下方法创建实际期末人数度量：
实际期末人数：=CALCULATE(SUM('Finance Data'[实际人数])，LASTNONBLANK('Finance Data'[日期]，IF(CALCULATE(SUM('Finance Data'[实际人数])，ALL(帐户))=0，BLANK()，CALCULATE(SUM('Finance Data'[实际人数])，ALL(帐户))))，ALL(帐户))
DAX LASTNONBLANK 函数按上述方式使用时返回您具有非空白人数总和的最后日期，以便您可以计算该日期的人数总和。
Creating Budget Ending Headcount Measure
您可以按如下方法创建预算末期人数度量：
预算末期人数：= CALCULATE(SUM('Finance Data'[预算人数])，LASTNONBLANK('Finance Data'[日期]，IF(CALCULATE(SUM('Finance Data'[预算人数])，ALL(帐户))=0，BLANK()，CALCULATE(SUM('Finance Data'[预算人数])，ALL(帐户))))，ALL(帐户))
Creating Forecast Ending Headcount Measure
您可以按如下方法创建预测期末人数度量：
预测期末人数：= CALCULATE(SUM('Finance Data'[预测人数])，LASTNONBLANK('Finance Data'[日期]，IF(CALCULATE(SUM('Finance Data'[预测人数])，ALL(帐户))=0，BLANK()，CALCULATE(SUM('Finance Data'[预测人数])，ALL(帐户))))，ALL(帐户))
Average Headcount Measures
在上一章节，您已学习如何计算特定期间的期末人数。同样地，您可以为任何给定月份的选择创建平均每月人数。
平均每月人数是各个月人数的总和除以选择中的月份数。
您可以使用 DAX AVERAGEX 函数创建这些计量。
Creating Actual Average Headcount Measure
您可以按如下方式创建实际平均人数计量：
实际平均人员编制：=AVERAGEX(VALUES('Finance Data'[Fiscal Month]), [Actual Ending Head Count])
Creating Budget Average Headcount Measure
您可以按如下方式创建实际平均人数计量：
预算平均人员编制：=AVERAGEX(VALUES('Finance Data'[Fiscal Month]), [Budget Ending Head Count])
Creating Forecast Average Headcount Measure
可以按如下创建预测平均人员编制度量值：
预测平均人员编制：=AVERAGEX(VALUES('Finance Data'[Fiscal Month]), [Actual Ending Head Count])
Total Headcount Measures
在前面的章节中，您已学习如何创建月计数度量和平均人数度量。可以使用这些度量计算基础人数度量 -
-
Actual Total Headcount
-
Budget Total Headcount
-
Forecast Total Headcount
在随后的章节中，您将学习如何在其他计算（如同比人数和差异度量）中使用这些基础人数度量。
YoY Headcount Measures and Analysis
在上一章中，您学习了如何创建基本员工人数度量值 – 即实际总员工人数、预算总员工人数和预测总员工人数。
在本章中，您将学习如何创建同比员工人数度量值以及如何使用这些度量值分析数据。
Creating Year-over-Year Actual Ending Headcount Measure
您可以按如下方式创建同比实际期末员工人数度量值 -
同比实际期末员工人数：=[实际期末员工人数]-[上一年实际期末员工人数]
Creating Year-over-Year Actual Average Headcount Measure
您可以按如下方式创建同比实际平均员工人数度量值 -
同比实际平均员工人数：= [实际平均员工人数]-[上一年实际平均员工人数]
Creating Year-over-Year Actual Total Headcount Measure
您可以按如下方式创建同比实际总员工人数度量值 -
同比实际总员工人数：=[实际总员工人数]-[上一年实际总员工人数]
Analyzing Data with Year-over-Year Actual Headcount Measures
按以下步骤创建 Power Pivot 透视表 −
-
将“财政季度”和“月份”字段从日期表添加到行中。
-
添加度量值 - 实际期末员工人数、上一年实际期末员工人数、同比实际期末员工人数到值中。
-
在“财政年度”字段中插入一个切片器。
-
在切片器中选择“2016 财政年度”。
在同一工作表上创建另一个 Power 透视表，如下所示：
-
将“财政季度”和“月份”字段从日期表添加到行中。
-
将度量（实际平均人数、上一年度实际平均人数和同比实际平均人数）添加到“值”。
将切片器连接到此透视表，如下所示：
-
Click the Slicer.
-
单击功能区上“切片器工具”下的“选项”选项卡。
-
Click Report Connections.
将出现“报表连接”对话框。
-
选择上述两个透视表。
-
Click OK.
Creating Year-over-Year Budget Ending Headcount Measure
您可以创建同比预算年末人数度量，如下所示：
同比预算年末人数：= [预算年末人数]-[上一年度实际年末人数]
Creating Year-over-Year Budget Average Headcount Measure
您可以创建同比预算平均人数度量，如下所示：
同比预算平均人数：= [预算平均人数]-[上一年度实际平均人数]
Creating Year-over-Year Budget Total Headcount Measure
您可以创建同比预算总人数度量，如下所示：
同比预算总人数：= [预算总人数]-[上一年度实际总人数]
Creating Year-over-Year Forecast Ending Headcount Measure
您可以创建同比预测年末人数度量，如下所示：
同比预测年末人数：= [预测年末人数]-[上一年度实际年末人数]
Variance Headcount Measures
您可以根据迄今为止创建的人数度量创建差异人数度量。
Creating Variance to Budget Ending Headcount Measure
您可以按如下方式创建预算变动结束时的人头数指标：
VTB Ending Head Count：= 'Finance Data'[Budget Ending Head Count]-'Finance Data'[Actual Ending Head Count]
Creating Variance to Budget Average Headcount Measure
您可以按如下方式创建预算变动平均人头数指标：
VTB Average Head Count：= 'Finance Data'[Budget Average Headcount]-'Finance Data'[Actual Average Headcount]
Creating Variance to Budget Total Headcount Measure
您可以按如下方式创建预算变动总人头数指标：
VTB Total Head Count：= 'Finance Data'[Budget Total Headcount]-'Finance Data'[Actual Total Head Count]
Creating Variance to Forecast Ending Headcount Measure
您可以按如下方式创建预测变动结束时的人头数指标：
VTF Ending Head Count：= 'Finance Data'[Forecast Ending Head Count]-'Finance Data'[Actual Ending Head Count]
Creating Variance to Forecast Average Headcount Measure
您可以按如下方式创建预测变动平均人头数指标：
VTF Average Head Count：= 'Finance Data'[Forecast Average Headcount]-'Finance Data'[Actual Average Headcount]
Creating Variance to Forecast Total Headcount Measure
您可以按如下方式创建预测变动总人头数指标：
VTF Total Head Count：= 'Finance Data'[Forecast Total Headcount]-'Finance Data'[Actual Total Head Count]
Creating Forecast Variance to Budget Ending Headcount Measure
您可以按如下方式创建预算的预测变动结束时的人头数指标：
Forecast VTB Ending Head Count：= 'Finance Data'[Budget Ending Head Count]-'Finance Data'[Forecast Ending Head Count]
Cost Per Headcount Measures and Analysis
您已了解关于度量值的两种主要类别：
-
Finance Measures.
-
Headcount Measures.
您将学习的第三个主要的度量值类别是人力成本度量值。任何组织都会希望知道人均年成本。人均年成本表示公司雇佣一位员工一整年的成本。
要创建人均成本度量值，您首先需要创建某些初步的人力成本度量值。在账户表中，您有一个列 - 子分类，其中包含人员作为值之一。因此，您可以在子分类列中对账户表应用筛选器，以获取财务数据表的筛选器上下文以获取人员成本。
您可以使用这样获取的人员成本度量值和月数计数度量值来创建年化人员成本度量值。最后，您可以从年化人员成本度量值和平均人员编制度量值创建年化人均成本度量值。
Creating Actual People Cost Measure
您可以按如下创建实际人员成本度量值：
实际人员成本：=CALCULATE('Finance Data'[Actual Sum], FILTER('Finance Data', RELATED(Accounts[Sub Class])="People"))
Creating Budget People Cost Measure
您可以按如下创建预算人员成本度量值：
预算人员成本：=CALCULATE('Finance Data'[Budget Sum], FILTER('Finance Data', RELATED(Accounts[Sub Class])="People"))
Creating Forecast People Cost Measure
您可以按如下创建预测人员成本度量值：
预测人员成本：=CALCULATE('Finance Data'[Forecast Sum], FILTER('Finance Data', RELATED(Accounts[Sub Class])="People"))
Creating Annualized Actual People Cost Measure
您可以按如下创建年化实际人员成本度量值：
年化实际人员成本：=IF([CountOfActualMonths],[Actual People Cost]*12/[CountOfActualMonths],BLANK())
Creating Annualized Budget People Cost Measure
您可以按如下方式创建年化预算人员成本指标：
Annualized Budget People Cost:=IF([CountOfBudgetMonths], [Budget People Cost]*12/[CountOfBudgetMonths],BLANK())
Creating Annualized Forecast People Cost Measure
您可以按如下方式创建年化预测人员成本指标：
Annualized Forecast People Cost:=IF([CountOfForecastMonths],[Forecast People Cost]*12/[CountOfForecastMonths],BLANK())
Creating Actual Annualized Cost Per Head Measure
您可以按如下方式创建实际年化人均成本 (CPH) 指标：
Actual Annualized CPH:=IF([Actual Average Headcount], [Annualized Actual People Cost]/[Actual Average Headcount],BLANK() )
Creating Budget Annualized Cost Per Head Measure
您可以按如下方式创建预算年化人均成本 (CPH) 指标：
Budget Annualized CPH:=IF([Budget Average Headcount],[Annualized Budget People Cost]/[Budget Average Headcount],BLANK())
Creating Forecast Annualized Cost Per Head Measure
您可以按如下方式创建预测年化人均成本 (CPH) 指标：
Forecast Annualized CPH:=IF([Forecast Average Headcount],[Annualized Forecast People Cost]/[Forecast Average Headcount], BLANK())
Creating Prior Year Actual Annualized Cost Per Head Measure
您可以按如下方式创建上一年实际年化人均成本 (CPH) 指标：
Prior Year Actual Annualized CPH:=CALCULATE([Actual Annualized CPH], DATEADD('Date'[Date],-1,YEAR) )
Analyzing Data with Cost Per Head Measures
按以下步骤创建 Power Pivot 透视表 −
-
将字段财政季度和财政月份从日期表添加到行中。
-
将指标“实际年化 CPH”、“预算年化 CPH”和“预测年化 CPH”添加到“列”。
-
将“日期”表中的“财政年度”字段添加到“筛选器”。
-
在“筛选器”中选择“FY2016”。
按如下方式创建另一张 Power Pivot 表：
-
将“日期”表中的“财政季度”字段添加到“行”。
-
将指标“实际年化 CPH”和“上一年实际年化 CPH”添加到“列”。
-
在“日期”表中的“财政年度”字段上插入切片器。
-
在切片器上选择 FY2015 和 FY2016。
Rate Variance and Volume Variance
您已了解如何创建全年人均成本和总人数指标。您可以使用这些指标创建比率差异和数量差异指标。
-
比率差异指标计算成本差异的哪一部分是由人均成本差异引起的。
-
数量差异指标计算有多少货币差异是由人数波动推动的。
Creating Variance to Budget Rate Measure
您可以按照以下方式创建预算比率差异指标：
VTB 比率:=([预算全年人均成本]/12-[实际全年人均成本]/12)*[实际总人数]
Analyzing Data with Variance to Budget Measures
按以下步骤创建 Power Pivot 透视表 −
-
将字段财政季度和财政月份从日期表添加到行中。
-
将指标实际全年人均成本、预算全年人均成本、VTB 比率、VTB 数量和 VTB 总计添加到值中。
-
在筛选器中添加来自日期表的财政年份字段和来自账户表的子类字段。
-
在财政年筛选器中选择 FY2016。
-
在子类筛选器中选择人员。
-
过滤财政季度值的栏标签 FY2016-Q1 和 FY2016-Q2。
您可以在上面的数据透视表中观察到以下内容：
-
显示的 VTB 总和值仅对于子类 - 人员。
-
对于财政季度 FY2016-Q1，VTB 总和为 4,705,568 美元，VTB 比率为 970,506,297 美元，VTB 量为 -965,800,727 美元。
-
VTB 比率维数计算出在预算差异（VTB 总和）中，970,506,297 美元是由成本/人头的差异导致，-965,800,727 美元是由人头数的差异导致。
-
如果您添加 VTB 比率和 VTB 量，您将获得 4,705,568 美元的值，该值与子类人员的 VTB 总和返回的值相同。
-
同样地，对于财政季度 FY2016-Q2，VTB 比率为 1,281,467,662 美元，VTB 量为 -1,210,710,978 美元。如果您添加 VTB 比率和 VTB 量，您将获得 70,756,678 美元，这是数据透视表中显示的 VTB 总和。
Creating Year-Over-Year Rate Measure
您可以按如下方法创建同比比率维数
同比比率 := （ [实际年化每小时生产率] / 12 - [上一年实际年化每小时生产率] / 12 ） * [实际总人数]
Analyzing Data with Variance to Forecast Measures
按以下步骤创建 Power Pivot 透视表 −
-
将字段财政季度和财政月份从日期表添加到行中。
-
将度量实际年化每小时生产率、预测年化每小时生产率、到预测比率的差异、到预测数量的差异、VTF 总和添加到值中。
-
在筛选器中添加来自日期表的财政年份字段和来自账户表的子类字段。
-
在财政年筛选器中选择 FY2016。
-
在子类筛选器中选择人员。
-
过滤财政季度值的栏标签 FY2016-Q1 和 FY2016-Q2。
