使用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

列印頁面