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

---

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

所有组织的决策者都已确定需要分析其组织的特定历史数据以及整个行业的历史数据。在当今竞争激烈的世界中，为了应对不断变化的业务挑战，这一点变得日益重要。

大数据和商业智能已成为商业界的流行语。数据源变得庞大，数据格式也变得多种多样。当前需要的是拥有简单易用的工具来在更短的时间内处理不断流动的大量数据，以获得洞察力并在适当的时间做出相关决策。

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

Microsoft Excel 有一个名为 Power Pivot 的强大工具，它在以前版本的 Excel 中作为加载项提供，并且是 Excel 2016 中的内置功能。Power Pivot 的数据库（称为数据模型）和有效的公式语言称为 DAX（数据分析表达式）的数据模型使 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 数据透视表使您能够分析数据并呈现结果。

在本教程中，您将学习使用 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 PivotTables。当您在后续章节中了解如何使用 DAX 创建度量时，您还将了解如何使用 Power PivotTables 通过这些度量来分析数据。

将数据加载到数据模型中

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

正如您所观察到的，您可以从数据库、数据服务或多种其他类型的数据源加载数据。

当您将数据从数据源加载到数据模型时，将与数据源建立连接。这使得当源数据发生变化时数据能够刷新。

开始新的数据模型

在本节中，您将学习如何对数据进行建模以进行损益分析。用于分析的数据位于 Microsoft Access 数据库中。

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

• 打开一个新的 Excel 工作簿
• 单击功能区上的 PowerPivot 选项卡
• 单击数据模型组中的管理

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

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

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

• 单击功能区上“获取外部数据”组中的“从数据库”。
• 单击下拉列表中的“来自访问”。

出现表导入向导对话框。

• 浏览到 Access 文件。

• 为连接指定一个友好的名称。

• 单击下一步按钮。将出现表导入向导的下一部分。

• 在表导入向导中，选择选项 - 从表和视图列表中选择以选择要导入的数据。

• 单击下一步按钮。表导入向导的下一部分将出现，如以下屏幕截图所示。

• 选择所有表。

• 为表指定友好的名称。这是必要的，因为这些名称出现在 Power PivotTables 中，因此每个人都应该理解。

选择表中的列

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

• 单击预览和过滤按钮。表导入向导的下一部分 - 所选表的预览 - 将出现。

• 如上面的屏幕截图所示，列标题有复选框。在所选表中选择要导入的列。

• 单击“确定”。对其他表重复相同的操作。

将数据导入数据模型

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

将显示导入状态。数据加载完成后，状态最终显示为Success。

查看数据模型中的数据

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

您可以观察以下内容 -

• 每个表都显示在单独的选项卡中。
• 选项卡名称是相应的表名称。
• 数据下方的区域用于计算。

查看连接名称

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

如上面的屏幕截图所示，给定的连接名称出现在 PowerPivot 数据连接下。

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

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

数据模型中的表

在上一章创建的数据模型中，有 3 个表 -

• 账户
• 地理 洛克
• 财务数据

确保适当的数据类型

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

• 单击表中的列。

• 请注意功能区格式组中显示的列的数据类型。

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

• 单击格式组中数据类型旁边的向下箭头。

• 在下拉列表中单击适当的数据类型。

• 对数据模型中所有表中的每一列重复此操作。

帐户表中的列

在帐户表中，您有以下列 -

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

Geography Locn 表中的列

Geography Locn 表包含有关每个利润中心的数据。

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

财务数据表中的列

在财务数据表中，您有以下列 -

数据模型中的表类型

Accounts 和 Geography Locn 表都是维度表，也称为查找表。

财务数据表是事实表，也称为数据表。财务数据表包含利润和分析计算所需的数据。您还将在此财务数据表中以度量和计算列的形式创建元数据，以便在继续学习本教程时对各种类型的损益计算的数据进行建模。

了解数据表

数据分析涉及浏览一段时间内的数据并进行跨时间段的计算。例如，您可能必须将本年度的利润与上一年的利润进行比较。同样，您可能必须预测未来几年的增长和利润。对于这些，您需要在一段时间内使用分组和聚合。

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 中打开一个新工作表。

• 类型 – 列第一行中的日期。

• 在同一列的第二行中键入要创建的日期范围中的第一个日期。

• 选择单元格，单击填充柄并将其向下拖动以在所需日期范围内创建一列连续日期。

例如，输入 1/1/2014，单击填充柄并向下拖动以填充截至 31/12/2016 的连续日期。

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

Excel 中的单列日期表已准备就绪。

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

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

将出现粘贴预览对话框，如下图所示。

• 在表名称框中键入日期。
• 预览数据。

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

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

现在，数据模型中有一个日期表，其中包含一列连续日期。该列的标题是您在 Excel 表格中给出的日期。

将新日期列添加到日期表

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

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

• 天

  =DAY('日期'[日期])

• 月

  =月份('日期'[日期])

• 年

  =年份('日期'[日期])

• 四分之一

  =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 表并将其粘贴到数据模型中的新表中。

• 将表命名为 Holidays。

• 接下来，您可以使用 DAX LOOKUPVALUE 函数将计算的假期列添加到日历表中。

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

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

向会计年度添加列

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

您可以使用 DAX 公式将会计时间段包含在日历表中 -

• 添加 FYE 度量

  财年:=3

• 添加以下计算列 -

  • 财政年度

    =IF('日历'[月]<='日历'[财年],'日历'[年],'日历'[年]+1)

  • 财政月

    =IF('日历'[月]<='日历'[财年],12-'日历'[财年]+'日历'[月],'日历'[月]-'日历'[财年] )

  • 财政季度

    =INT(('日历'[财政月份]+2)/3)

设置日期表属性

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

设置日期表属性 -

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

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

扩展数据模型

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

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

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

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

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

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

• 要创建数据表（即财务数据表和日期表）之间的关系，您需要在财务数据表中创建计算列“日期”。

• 要执行不同类型的计算，您需要在数据表（财务数据）和查找表（帐户和地理区域）之间创建关系。

• 您需要创建各种度量来帮助您执行多项计算并进行所需的分析。

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

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

将日期表添加到数据模型

为跨越财政年度的时间段创建一个日期表，如下所示 -

• 在新的 Excel 工作表中创建一个包含单列的表格，该表格的标题为“日期”和范围从 2011 年 7 月 1 日到 2018 年 6 月 30 日的连续日期。

• 从 Excel 复制表格并将其粘贴到 Power Pivot 窗口中。这将在 Power Pivot 数据模型中创建一个新表。

• 将表命名为“日期”。

• 确保“日期”表中的“日期”列的数据类型为“日期”(DateTime)。

接下来，您需要将计算列 - 财政年度、财政季度、财政月份和月份添加到日期表中，如下所示 -

财政年度

假设会计年度结束日期为 6 月 30^日。那么，一个财政年度从7月1^日到6月30^日。例如，2011年7月1^日（7/1/2011）到2012年6月30^日（6/30/2012）期间将是2012财年。

在“日期”表中，假设您希望表示与 FY2012 相同的内容。

• 您需要首先提取日期的财政年度部分并附加 FY。

  • 对于 2011 年 7 月至 2011 年 12 月期间的日期，财政年度为 1+2011。

  • 对于 2012 年 1 月至 2012 年 6 月期间的日期，财政年度为 0+2012。

  • 概括而言，如果财政年度结束月份是 FYE，请执行以下操作 -

    ((月 – 1)/FYE) + 年的整数部分

  • 接下来，取最右边的 4 个字符来获取财政年度。

• 在 DAX 中，您可以表示为 -

  RIGHT(INT((MONTH('日期'[日期])-1)/'日期'[财政年度])+YEAR('日期'[日期]),4)

• 使用 DAX 公式在日期表中添加计算列会计年度 -

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

财政季度

如果 FYE 代表财政年度结束月份，则财政季度的获取方式为

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

• 在 DAX 中，您可以表示为 -

  INT((MOD(月份('日期'[日期])+'日期'[财政年]-1,12)+3)/3)

• 使用 DAX 公式在日期表中添加计算列财政季度 -

  ='日期'[财政年度]&"-Q"&FORMAT( INT((MOD(MONTH('日期'[日期]) + '日期'[财政年度]-1,12) + 3)/3), "0" ）

财政月

如果 FYE 代表财政年度结束，则财政月份期间的获取方式为

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

• 在 DAX 中，您可以表示为 -

  MOD(MONTH('日期'[日期])+'日期'[财政年]-1,12)+1

• 使用 DAX 公式在日期表中添加计算列会计月份 -

  ='日期'[财政年度]&"-P" & FORMAT(MOD(月份([日期])+[财政年度]-1,12)+1,"00")

月

最后，添加代表财政年度中月份编号的计算列 Month，如下所示 -

=格式(MOD(月([日期])+[财政年]-1,12)+1,"00") & "-" & 格式([日期],"mmm")

生成的日期表类似于以下屏幕截图。

将表 - 日期标记为日期表，并将列 - 日期标记为具有唯一值的列，如以下屏幕截图所示。

添加计算列

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

• 使用 DAX 公式在财务数据表中添加计算列日期 -

  = DATEVALUE ('财务数据'[财政月份])

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

数据模型中有以下表格 -

• 数据表-财务数据
• 查找表 - 账户和地理 Locn
• 日期表 - 日期

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

• 查看 Power Pivot 图表视图中的表格。

• 在表之间创建以下关系 -

  • 财务数据表和帐户表与帐户列之间的关系。

  • Finance Data 表和 Geography Locn 表与利润中心列之间的关系。

  • 财务数据表和日期表与日期列之间的关系。

从客户端工具中隐藏列

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

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

• 在财务数据表中选择列 - 会计月份、日期、帐户和利润中心。

• 右键单击并在下拉列表中选择从客户端工具隐藏。

在表中创建度量

您已准备好使用数据模型和 Power PivotTables 通过 DAX 进行数据建模和分析。

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

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

基础财务措施和分析

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

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

您可以对任何业务和环境应用相同的数据建模和分析方法

根据财务数据创建衡量指标

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

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

在以下部分中，您将学习如何创建基本财务指标并使用这些指标分析数据。这些措施被称为基本措施，因为它们可用于创建其他财务措施。您还将学习如何为之前的时间段创建度量并在分析中使用它们。

创建基础财务措施

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

预算

预算是对公司一个财政年度的收入和支出的估计。预算是在财政年度开始时计算的，同时考虑到公司的目标和指标。在财政年度中需要不时分析预算措施，因为市场状况可能会发生变化，公司可能必须根据行业当前趋势调整其目标和目标。

预报

财务预测是通过检查公司的收入和支出的历史数据来估计公司未来的财务结果。您可以使用财务预测进行以下操作 -

• 确定如何分配未来时期的预算。

• 跟踪公司的预期业绩。

• 及时做出决定来解决目标的不足，或最大限度地利用新出现的机会。

实际值

要执行预算和预测计算，您需要任何时间点的实际收入和支出。

您可以创建以下 3 个基本财务指标，这些指标可用于在数据模式下创建其他财务指标 -

• 预算金额
• 实际金额
• 预测总和

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

创建基本财务措施如下 -

预算金额

预算总和:=SUM('财务数据'[预算])

实际金额

实际总和：=SUM('财务数据'[实际])

预测总和

预测总和:=SUM('财务数据'[预测])

使用基本财务指标分析数据

通过基本财务指标和日期表，您可以执行以下分析 -

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

制定前期财务措施

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

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

上季度实际总和：=CALCULATE([实际总和], DATEADD('日期'[日期],1,QUARTER))

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

上一年实际金额：=CALCULATE([实际金额], DATEADD('日期'[日期],1,YEAR))

使用前期财务指标分析数据

使用基本度量、前期度量和日期表，您可以按如下方式执行分析 -

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

同比财务指标和分析

同比 (YoY) 是衡量增长的指标。是用实际总额减去上年实际总额得出的。

如果结果为正，则反映实际增加，如果结果为负，则反映实际减少，即如果我们按年计算：

同比=（实际金额-上年实际金额）

• 如果实际金额>上年实际金额，则同比为正。
• 如果实际金额<上年实际金额，则同比为负数。

在财务数据中，费用账户等账户将具有借方（正）金额，收入账户将具有贷方（负）金额。因此，对于费用账户，上述公式效果很好。

然而，对于收入账户，则应该相反，即

• 如果实际金额>上年实际金额，则同比应为负数。
• 如果实际金额 < 上一年实际金额，则同比应为正数。

因此，对于收入账户，您必须逐年计算如下：

同比=-（实际金额-上年实际金额）

创建逐年衡量指标

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

YoY:=IF(CONTAINS(账户、账户[类别],"净收入"),-([实际金额]-[上年实际金额]),[实际金额]-[上年实际金额])

在上面的 DAX 公式中 -

• 如果帐户表中的类列中有“净收入”，则 DAX CONTAINS 函数返回 TRUE。

• DAX IF 函数然后返回 –([实际总和]-[上一年实际总和])。

• 否则，DAX IF 函数返回[实际总和]-[上一年实际总和]。

创建同比百分比衡​​量标准

您可以将同比表示为百分比，其比率为 -

（同比）/（上年实际金额）

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

同比%:=IF([上年实际金额],[同比]/ABS([上年实际金额]),BLANK())

上面的公式中使用了DAX IF函数来保证不被零除。

使用同比指标分析数据

创建一个 Power PivotTable 如下 -

• 将字段“类”和“子类”从“帐户”表添加到“行”。
• 将度量值 - 实际总额、上一年实际总额、同比和同比百分比添加到值中。
• 在“日期”表中的“会计年度”字段上插入切片器。
• 在切片器中选择 FY2016。

创建年度预算衡量指标

您可以按如下方式创建预算年度衡量指标 -

预算同比：= IF(CONTAINS(账户、账户[类],"净收入"), - ([预算总额] - [上一年实际总额]), [预算总额] - [上一年实际总额])

创建预算同比百分比衡​​量标准

您可以创建预算同比百分比度量，如下所示 -

预算同比 %:=IF([上年实际金额] , [预算同比] / ABS ([上年实际金额]) , BLANK())

使用预算同比指标分析数据

创建一个 Power PivotTable 如下 -

• 将字段“类”和“子类”从“帐户”表添加到“行”。
• 添加度量 – 预算总额、上一年实际总额、预算同比和预算同比百分比到值。
• 在“日期”表中的“会计年度”字段上插入切片器。
• 在切片器中选择 FY2016。

创建预测同比指标

您可以创建预测同比测量，如下所示 -

同比预测:=IF(CONTAINS(账户,账户[类],"净收入"), - ([预测总和] - [上一年实际总和]), [预测总和] - [上一年实际总和])

创建预测同比百分比度量

您可以创建预测同比百分比度量，如下所示 -

预测同比%:=IF([上年实际金额],[预测同比]/ABS([上年实际金额]),BLANK())

使用预测同比指标分析数据

创建一个 Power PivotTable 如下 -

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

方差测量和分析

您可以创建差异度量，例如预算差异、预测差异和预算预测差异。您还可以根据这些指标分析财务数据。

创建预算总和度量的差异

创建预算总和度量的差异（VTB总和）如下 -

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 时间智能函数。这将使您能够分析月份级别的数据。

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

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

创建年初至今的实际总和度量，如下所示 -

YTD 实际总和:=TOTALYTD([实际总和], '日期'[日期], ALL('日期'), "6/30")

创建年初至今的预算总额衡量标准

创建年初至今的预算总和措施如下 -

YTD 预算总额:=TOTALYTD([预算总额], '日期'[日期], ALL('日期'), "6/30")

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

创建年初至今的预测总和度量，如下所示 -

YTD 预测总和：=TOTALYTD([预测总和], '日期'[日期], ALL('日期'), "6/30")

创建上一年度迄今的实际总和计量

创建上一年至今的实际总和度量，如下所示 -

上一年至今实际总和：=TOTALYTD([上一年实际总和], '日期'[日期], ALL('日期'), "6/30")

使用年初至今的指标分析数据

创建一个 Power PivotTable 如下 -

• 将日期表中的月份添加到行。

• 将财务数据表中的度量“实际总和”、“年初至今实际总和”、“年初至今预算总和”和“年初至今预测总和”添加到“值”。

• 在日期表中的会计年度上插入切片器。

• 在切片器中选择 FY2016。

创建一个 Power PivotTable 如下 -

• 将日期表中的月份添加到行。

• 将“财务数据”表中的“实际总和”、“年初至今实际总和”、“上一年实际总和”和“上一年年初至今实际总和”度量添加到“值”。

• 在日期表中的会计年度上插入切片器。

• 在切片器中选择 FY2016。

季度至今的衡量和分析

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

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

创建季度至今总和度量

创建季度至今的实际总和度量，如下所示 -

QTD 实际金额:=TOTALQTD([实际金额],'日期'[日期],ALL('日期'))

创建季度至今的预算总额指标

创建季度至今的预算总和度量如下 -

QTD 预算总额:=TOTALQTD([预算总额], '日期'[日期], ALL('日期'))

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

创建季度至今的预算总和度量如下 -

QTD 预算总额:=TOTALQTD([预算总额], '日期'[日期], ALL('日期'))

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

创建季度至今预测总和度量，如下所示 -

QTD 预测总和:=TOTALQTD([预测总和], '日期'[日期], ALL('日期'))

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

创建先前季度至今的实际总和度量，如下所示 -

上一季度实际总和：=TOTALQTD([上一季度实际总和], '日期'[日期], ALL('日期'))

使用季度至今的指标分析数据

创建一个 Power PivotTable 如下 -

• 将会计月份从日期表添加到行。

• 将财务数据表中的度量“实际总和”、“QTD 实际总和”、“QTD 预算总和”和“QTD 预测总和”添加到“值”。

• 在日期表中的财政季度上插入切片器。

• 在切片器中选择 FY2016-Q2。

创建一个 Power PivotTable 如下 -

• 将会计月份从日期表添加到行。

• 将“财务数据”表中的“实际总和”、“QTD 实际总和”、“上季度实际总和”和“上一 QTD 实际总和”度量添加到“值”。

• 在“日期”表的“财政季度”中插入一个切片器。

• 在切片器中选择 FY2016-Q1。

预算措施与分析

预算涉及估计公司在一个财政年度的现金流量。预算中会考虑公司的财务状况、目标、预期收入和支出。

然而，市场状况在本财政年度可能会发生变化，公司可能不得不重新设定目标。这需要使用财政年度开始时估计的预算（预算总额）和财政年度开始至今的实际支出总额（年初至今实际总额）来分析财务数据。

在一个财政年度的任何时间，您可以计算以下内容 -

未用余额

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

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

预算完成率%

预算完成百分比是您迄今为止已花费的预算的百分比，即

预算完成百分比 = 年初至今实际金额/年初至今预算金额

这些计算可以帮助那些使用预算来做出决策的公司。

创建未支出余额措施

您可以创建未支出余额度量，如下所示 -

未支出余额:=CALCULATE( [年初至今预算总额],ALL('财务数据'[日期]) )-[年初至今实际总额]

创建预算完成百分比衡量标准

您可以创建预算完成百分比衡量标准，如下所示 -

预算完成率 %:=IF([年初至今预算总额],[年初至今实际总额]/CALCULATE([年初至今预算总额],ALL('财务数据'[日期])),BLANK())

使用预算措施分析数据

创建一个 Power PivotTable 如下 -

• 将日期表中的月份添加到行中。

• 将“财务数据”表中的“预算总额”、“年初至今预算总额”、“年初至今实际总额”、“预算完成百分比”和“未支出余额”度量添加到“值”中。

• 在会计年度字段中插入切片器。

• 在切片器中选择 FY2016。

预测措施与分析

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

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

在一个财政年度的任何时间，您可以计算以下内容 -

预测完成率%

预测达到百分比是您迄今为止已花费的预测金额的百分比，即

预测完成百分比 = 年初至今实际金额/年初至今预测金额

预测未支出余额

预测未支出余额是实际支出后剩余的预测金额，即

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

预算调整

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

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

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

创建预测完成百分比度量

您可以创建预测达到百分比度量，如下所示 -

预测达成百分比：= IF([年初至今预测总和], [年初至今实际总和]/[年初至今预测总和], BLANK())

创建预测未支出余额度量

您可以创建预测未支出余额度量，如下所示 -

预测未支出余额：=[年初至今预测金额]-[年初至今实际金额]

创建预算调整措施

您可以创建预算调整措施如下 -

预算调整：=[预计未用余额]-[未用余额]

使用预测措施分析数据

创建一个 Power PivotTable 如下 -

• 将日期表中的月份添加到行。

• 将“财务数据”表中的“预算总额”、“年初至今预算总额”、“年初至今实际总额”、“预算完成百分比”和“未支出余额”度量添加到“值”中。

• 在财政年度插入切片器。

• 在切片器中选择 FY2016。

月份计数措施

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

创建实际月份计数度量

您可以将实际月份计数度量创建为