Data-modeling-with-dax-quick-guide
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のデータモデリングは、次の手順で構成されています-
- データの整形
- データの読み込み
- テーブル間の関係を定義する
- データ型の定義
- 新しいデータ洞察の作成
データの形成
データモデルの構築に必要なデータは、さまざまなソースからのものであり、さまざまな形式である可能性があります。 これらの各データソースのデータのどの部分が特定のデータ分析に必要かを判断する必要があります。 これは、データのシェーピングと呼ばれます。
たとえば、組織内のすべての従業員のデータを取得する場合、各従業員のどの詳細が現在のコンテキストに関連するかを決定する必要があります。 つまり、従業員表のどの列をインポートする必要があるかを判断する必要があります。 これは、データモデルのテーブルの列数が少ないほど、テーブルの計算が高速になるためです。
データの読み込み
特定されたデータ（各テーブルで選択された列を持つデータテーブル）をロードする必要があります。
テーブル間の関係の定義
次に、さまざまなテーブル間の論理関係を定義する必要があります。これにより、これらのテーブルのデータの結合が容易になります。 テーブルがある場合-製品-製品に関するデータを含む-テーブル-販売-製品のさまざまな販売トランザクションで、2つのテーブル間の関係を定義することにより、製品ごとの売上を要約できます。
データ型の定義
データモデル内のデータに適切なデータ型を識別することは、計算の正確性にとって重要です。 インポートした各テーブルの列ごとに、データ型を定義する必要があります。 たとえば、テキストデータ型、実数データ型、整数データ型など。
新しいデータ洞察の作成
これは、BIの日付モデリングの重要なステップです。 構築されるデータモデルは、データの傾向を理解し、非常に短い時間で必要な決定を下す必要がある複数の人々と共有する必要があります。 したがって、ソースデータから新しいデータの洞察を作成すると効果的で、分析のやり直しを回避できます。
新しいデータの洞察は、特定のビジネスマンが簡単に理解して使用できるメタデータの形にすることができます。
データ分析
データモデルの準備ができたら、要件に従ってデータを分析できます。 分析結果を提示することも重要なステップです。なぜなら、決定はレポートに基づいて行われるからです。
Excel Power Pivotを使用したデータモデリング
Microsoft Excel Power Pivotは、データのモデリングと分析のための優れたツールです。
- データモデルはPower Pivotデータベースです。
- DAXは、DAX数式を使用して、データモデルのデータを使用してメタデータを作成するために使用できる数式言語です。
- データモデルのデータとメタデータを使用して作成されたExcelのPowerPivotTablesを使用すると、データを分析して結果を表示できます。
このチュートリアルでは、Power PivotデータモデルとDAXを使用したデータモデリングと、Power Pivotを使用したデータ分析について学習します。 Power Pivotを初めて使用する場合は、Excel Power Pivotチュートリアルを参照してください。
前の章-データモデリングと分析の概念-でデータモデリングプロセスのステップを学習しました。 この章では、Power PivotデータモデルとDAXを使用してこれらの各ステップを実行する方法を学習します。
次のセクションでは、Power Pivotデータモデルに適用されるこれらの各プロセスステップと、DAXの使用方法を学習します。
データの形成
Excel Power Pivotでは、さまざまな種類のデータソースからデータをインポートでき、インポート中にインポートするテーブルと列を表示および選択できます。
- データソースを特定します。
- データソースの種類を見つけます。 たとえば、データベースまたはデータサービス、またはその他のデータソース。
- 現在のコンテキストに関連するデータを決定します。
- データに適切なデータ型を決定します。 Power Pivotデータモデルでは、テーブルの列全体に対して1つのデータ型のみを使用できます。
- どのテーブルがファクトテーブルであり、どのテーブルがディメンションテーブルであるかを特定します。
- テーブル間の関連する論理関係を決定します。
データモデルへのデータのロード
リボンのPower Pivotウィンドウで提供されるいくつかのオプションを使用して、データをデータモデルに読み込むことができます。 これらのオプションは、グループ「外部データの取得」にあります。
「データモデルへのデータの読み込み」の章で、Accessデータベースからデータモデルにデータを読み込む方法を学習します。
説明のために、損益データを含むAccessデータベースが使用されます。
データモデルでのデータ型の定義
Power Pivotのデータモデリングプロセスの次のステップは、データモデルに読み込まれるテーブルの列のデータ型を定義することです。
「データモデルでのデータ型の定義」の章で、テーブルの列のデータ型を定義する方法を学習します。
テーブル間の関係の作成
Power Pivotのデータモデリングプロセスの次のステップは、データモデル内のテーブル間の関係を作成することです。
「データモデルの拡張」の章で、テーブル間の関係を作成する方法を学習します。
新しいデータ洞察の作成
データモデルでは、新しいデータの洞察を作成するために必要なメタデータを作成することができます-
- 計算列の作成
- 日付表の作成
- メジャーの作成
その後、[ピボットテーブルフィールド]リストのフィールドとして表示されるテーブルとメジャーの列に基づく動的なパワーピボットテーブルを作成して、データを分析できます。
計算列の追加
テーブルの計算列は、DAX数式を使用してテーブルに追加する列です。
「データモデルの拡張」の章で、データモデルのテーブルに計算列を追加する方法を学習します。
日付表の作成
DAX数式でタイムインテリジェンス関数を使用してメタデータを作成するには、日付テーブルが必要です。 日付表を初めて使用する場合は、「日付表について」の章を参照してください。
「データモデルの拡張」の章で、データモデルに日付テーブルを作成する方法を学習します。
メジャーの作成
現在のコンテキストでのデータ分析に必要なさまざまな計算にDAX関数とDAX数式を使用して、データテーブルにさまざまなメジャーを作成できます。
これは、DAXを使用したデータモデリングの重要なステップです。
後続の章で、損益分析のさまざまな目的のためにメジャーを作成する方法を学習します。
Power PivotTablesを使用したデータの分析
損益分析のファセットごとにパワーピボットテーブルを作成できます。 後続の章でDAXを使用してメジャーを作成する方法を学習すると、Power PivotTablesを使用してこれらのメジャーでデータを分析する方法も学習します。
データモデルへのデータのロード
さまざまなタイプのデータソースからデータモデルにデータをロードできます。 このために、Power Pivotウィンドウのリボンの[外部データの取得]グループにさまざまなオプションがあります。
ご覧のとおり、データベースから、またはデータサービスまたは他のいくつかのタイプのデータソースからデータをロードできます。
データをデータソースからデータモデルにロードすると、データソースとの接続が確立されます。 これにより、ソースデータが変更されたときにデータを更新できます。
新しいデータモデルで開始する
このセクションでは、損益分析のためにデータをモデル化する方法を学びます。 分析用のデータは、Microsoft Accessデータベースにあります。
次のように新しいデータモデルを開始できます-
- 新しいExcelブックを開く
- リボンの[PowerPivot]タブをクリックします
- [データモデル]グループの[管理]をクリックします
Power Pivotウィンドウが表示されます。 データをまだロードしていないため、ウィンドウは空白になります。
Accessデータベースからデータモデルへのデータの読み込み
Accessデータベースからデータをロードするには、次の手順を実行します-
- リボンの[外部データの取得]グループで[データベースから]をクリックします。
- ドロップダウンリストで[アクセスから]をクリックします。
テーブルインポートウィザードダイアログボックスが表示されます。
- Accessファイルを参照します。
- 接続のわかりやすい名前を付けます。
- [次へ]ボタンをクリックします。 テーブルインポートウィザードの次の部分が表示されます。
- テーブルインポートウィザードで、オプションを選択します-テーブルとビューのリストから選択して、インポートするデータを選択します。
- [次へ]ボタンをクリックします。 次のスクリーンショットに示すように、テーブルインポートウィザードの次の部分が表示されます。
- すべてのテーブルを選択します。
- テーブルにわかりやすい名前を付けます。 これは、これらの名前がPower PivotTablesに表示されるため、すべての人が理解する必要があるためです。
テーブル内の列の選択
現在の分析では、選択したテーブルのすべての列が必要なわけではありません。 したがって、データの整形中に選択した列のみを選択する必要があります。
- [プレビューとフィルター]ボタンをクリックします。 テーブルインポートウィザードの次の部分-選択したテーブルのプレビュー-が表示されます。
- 上記のスクリーンショットに見られるように、列ヘッダーにはチェックボックスがあります。 選択したテーブルでインポートする列を選択します。
- OKをクリックしてください。 他のテーブルについても同じことを繰り返します。
データモデルへのデータのインポート
データモデルにデータをロードする最後の段階です。 テーブルインポートウィザードの[完了]ボタンをクリックします。 テーブルインポートウィザードの次の部分が表示されます。
インポートのステータスが表示されます。 データのロードが完了すると、ステータスに最終的に成功が表示されます。
データモデルのデータの表示
インポートされたテーブルがPower Pivotウィンドウに表示されます。 これはデータモデルのビューです
あなたは次を観察することができます-
- 各テーブルは個別のタブに表示されます。
- タブ名はそれぞれのテーブル名です。
- データの下の領域は計算用です。
接続名の表示
[外部データの取得]グループの[既存の接続]をクリックします。 次のスクリーンショットに示すように、既存の接続ダイアログボックスが表示されます。
上記のスクリーンショットに見られるように、指定された接続名はPowerPivot Data Connectionsの下に表示されます。
データモデルでのデータ型の定義
Power Pivotデータモデルでは、列のデータ全体が同じデータ型である必要があります。 正確な計算を行うには、データモデルの各テーブルの各列のデータ型が要件に従っていることを確認する必要があります。
データモデルのテーブル
前の章で作成されたデータモデルでは、3つのテーブルがあります-
- アカウント
- Geography Locn
- 財務データ
適切なデータ型の確保
テーブルの列が必要なとおりであることを確認するには、Power Pivotウィンドウでデータ型を確認する必要があります。
- 表の列をクリックします。
- [書式設定]グループのリボンに表示される列のデータ型に注意してください。
選択した列のデータ型が適切でない場合は、次のようにデータ型を変更します。
- [書式設定]グループのデータ型の横の下矢印をクリックします。
- ドロップダウンリストで適切なデータ型をクリックします。 *データモデル内のすべてのテーブルのすべての列に対して繰り返します。
アカウントテーブルの列
アカウント表には、次の列があります-
| Sr.No | Column & Description |
|---|---|
| 1 |
行ごとに1つのアカウント番号が含まれます。 この列には一意の値があり、Finance Dataテーブルとの関係の定義に使用されます。 |
| 2 |
Class 各アカウントに関連付けられたクラス。 例-費用、純収入など |
| 3 |
Sub Class 費用または収益の種類を説明します。 例–人々。 |
Accountsテーブルのすべての列は本質的に説明的なものであるため、Textデータ型です。
Geography Locnテーブルの列
Geography Locnテーブルには、各利益センタに関するデータが含まれています。
列利益センタには、行ごとに1つの利益センタIDが含まれています。 この列には一意の値があり、Finance Dataテーブルとの関係の定義に使用されます。
財務データテーブルの列
財務データの表には、次の列があります-
| Column | Description | Data type |
|---|---|---|
| Fiscal Month | Month and Year | Text |
| Profit Center | Profit Center identity | Text |
| Account |
Account number. 各アカウントは複数の利益センターを持つことができます。 |
Text |
| Budget | Monthly budget amounts for each Profit Center. | Currency |
| Actual | Monthly actual amounts for each Profit Center. | Currency |
| Forecast | Monthly forecast amounts for each profit center. | Currency |
| Actual People | Month end actual number of employees for each Profit Center of each people Account. | Whole Number |
| Budget People | Month end budget number of employees for each Profit Center of each people Account. | Whole Number |
| Forecast People | Month end forecast number of employees for each Profit Center of each people Account. | Whole Number |
データモデルのテーブルの種類
AccountsテーブルとGeography Locnテーブルはどちらも、* lookupテーブル*とも呼ばれるディメンションテーブルです。
- 財務データテーブル*は、データテーブルとも呼ばれるファクトテーブルです。 財務データテーブルには、利益と分析の計算に必要なデータが含まれています。 また、このチュートリアルを進めるときに、さまざまなタイプの損益計算のデータをモデル化するために、このファイナンスデータテーブルにメジャーと計算列の形式でメタデータを作成します。
データテーブルについて
データ分析には、長期間にわたるデータの閲覧と期間にわたる計算が含まれます。 たとえば、今年度の利益を前年の利益と比較する必要がある場合があります。 同様に、今後数年間の成長と利益を予測する必要があるかもしれません。 これらの場合、一定期間にわたってグループ化と集計を使用する必要があります。
DAXは、そのような計算のほとんどを実行するのに役立ついくつかのタイムインテリジェンス機能を提供します。 ただし、これらのDAX関数では、データモデル内の他のテーブルで使用するためにDateテーブルが必要です。
データソースから他のデータとともに日付テーブルをインポートするか、データモデルで自分で日付テーブルを作成できます。
この章では、日付テーブルのさまざまな側面を理解します。 Power Pivotデータモデルの日付テーブルに精通している場合は、この章をスキップして以降の章に進むことができます。 それ以外の場合は、Power Pivotデータモデルの日付テーブルを理解できます。
日付表とは何ですか？
日付テーブルは、データモデル内のテーブルであり、必要な期間にわたって少なくとも1つの連続した日付の列があります。 異なる期間を表す追加の列を持つことができます。 ただし、必要なのは、DAXタイムインテリジェンス関数で必要とされる連続した日付の列です。
例えば、
- 日付テーブルには、日付、会計月、会計四半期、会計年度などの列を含めることができます。
- 日付テーブルには、日付、月、四半期、年などの列を含めることができます。
連続した日付を持つ日付テーブル
暦年の範囲で計算する必要があるとします。 次に、日付テーブルには、その特定の暦年のすべての日付を含む、一連の連続した日付を持つ少なくとも1つの列が必要です。
たとえば、閲覧するデータの日付が2014年4月1日から2016年11月30日までであるとします。
- 暦年についてレポートする必要がある場合、2014年1月1日から2016年12月31日までのすべての日付を順番に含む日付列の日付テーブルが必要です。
- 会計年度についてレポートする必要があり、会計年度末が6月30日である場合、2013年7月1日から6月30日までのすべての日付を含む日付列の日付テーブルが必要です。 th ^、2017年のシーケンス。
- カレンダーと会計年度の両方についてレポートする必要がある場合、必要な日付範囲にまたがる単一の日付テーブルを作成できます。
日付テーブルには、指定された期間の毎年の範囲のすべての日が含まれている必要があります。 したがって、その期間内に連続した日付を取得します。
定期的に新しいデータでデータを更新すると、終了日が1〜2年延長されるため、Dateテーブルを頻繁に更新する必要はありません。
日付テーブルは、次のスクリーンショットのようになります。
データモデルへの日付テーブルの追加
次のいずれかの方法で、データモデルに日付テーブルを追加できます-
- リレーショナルデータベースまたはその他のデータソースからのインポート。
- Excelで日付テーブルを作成し、Power Pivotで新しいテーブルにコピーまたはリンクします。
- Microsoft Azure Marketplaceからのインポート。
Excelで日付テーブルを作成し、データモデルにコピーする
Excelで日付テーブルを作成してデータモデルにコピーすることは、データモデルでデータテーブルを作成する最も簡単で柔軟な方法です。
- Excelで新しいワークシートを開きます。
- タイプ-列の最初の行の日付。
- 同じ列の2行目に作成する日付範囲の最初の日付を入力します。
- セルを選択し、フィルハンドルをクリックして下にドラッグし、必要な日付範囲で連続した日付の列を作成します。
たとえば、1/1/2014と入力し、フィルハンドルをクリックして、2016年12月31日までの連続する日付を埋めるために下にドラッグします。
- [日付]列をクリックします。
- リボンの[挿入]タブをクリックします。
- テーブルをクリックします。
- テーブル範囲を確認します。
- OKをクリックしてください。
日付の単一列の表は、Excelで準備ができています。
- テーブルを選択します。
- リボンの[コピー]をクリックします。
- [Power Pivot]ウィンドウをクリックします。
- リボンの[貼り付け]をクリックします。
これにより、クリップボードの内容がデータモデルの新しいテーブルに追加されます。 したがって、同じ方法を使用して、既存のデータモデルに日付テーブルを作成することもできます。
次のスクリーンショットに示すように、プレビューの貼り付けダイアログボックスが表示されます。
- [テーブル名]ボックスに「日付」と入力します。
- データをプレビューします。
- チェックボックス-最初の行を列ヘッダーとして使用します。
- OKをクリックしてください。
これにより、クリップボードの内容がデータモデルの新しいテーブルにコピーされます。
これで、連続した日付の単一列を持つデータモデルに日付テーブルができました。 列のヘッダーは、Excelテーブルで指定した日付です。
日付テーブルへの新しい日付列の追加
次に、計算の要件に従って、計算された列を日付テーブルに追加できます。
たとえば、次のように、日、月、年、および四半期の列を追加できます-
- Day
DAY（ 'Date' [Date]）
- 月
MONTH（ 'Date' [Date]）
- Year
YEAR（ 'Date' [Date]）
- 四半期
CONCATENATE（ "QTR"、INT（（ 'Date' [Month] +2）/3））
結果のデータモデルの日付テーブルは、次のスクリーンショットのようになります。
したがって、任意の数の計算列を日付テーブルに追加できます。 重要で必要なのは、日付テーブルに、計算を実行する期間にわたる連続した日付の列が必要であることです。
暦年の日付表の作成
暦年には、通常、1月1日から12月31日までの日付が含まれ、その特定の年にマークされた休日も含まれます。 計算を実行するときは、週末と祝日を除く稼働日のみを考慮する必要があります。
2017年の暦年の日付テーブルを作成するとします。
- 2017年1月1日から2017年12月31日までの連続した日付で構成される列Dateを含むExcelテーブルを作成します。 （これを行う方法については、前のセクションを参照してください。）
- Excelテーブルをコピーして、データモデルの新しいテーブルに貼り付けます。 （これを行う方法については、前のセクションを参照してください。）
- テーブルにCalendarという名前を付けます。
- 次の計算列を追加します-
- 日= DAY（ 'Calendar' [日付]）
- 月= MONTH（ 'Calendar' [日付]）
- 年= YEAR（ 'カレンダー' [日付]）
- 曜日= FORMAT（ 'Calendar' [Date]、 "DDD"）
- 月の名前= FORMAT（ 'Calendar' [Date]、 "MMM"）
カレンダーテーブルへの休日の追加
次のように休日をカレンダーテーブルに追加します-
- その年の宣言された休日のリストを取得します。
- たとえば、米国の場合、次のリンクhttp://www.calendar-365.com/2017-calendarl[[[1]] ]。
- それらをコピーしてExcelワークシートに貼り付けます。
- Excelテーブルをコピーして、データモデルの新しいテーブルに貼り付けます。
- テーブルにHolidaysという名前を付けます。
- 次に、DAX LOOKUPVALUE関数を使用して、休日の計算列をカレンダーテーブルに追加できます。
- = LOOKUPVALUE（Holidays [Holiday]、Holidays [Date]、 'Calendar' [Date]）*
DAX LOOKUPVALUE関数は、3番目のパラメーター、つまり 2番目のパラメーターのCalendar [Date]、つまり Holidays [Date]と最初のパラメーター、つまり 休日がある場合は[休日]。 結果は、次のスクリーンショットのようになります。
会計年度への列の追加
通常、会計年度には、会計年度末から翌会計年度末までの月の1 ^ st ^からの日付が含まれます。 たとえば、会計年度末が3月31日である場合、会計年度の範囲は4月1日から3月31日です。
あなたは、DAXの式を使用してカレンダー期間に会計期間を含めることができます-
*FYEのメジャーを追加
+* 年度：= 3 *
* 次の計算列を追加します-
* *会計年度*IF（ 'Calendar' [Month] ⇐ 'Calendar' [FYE]、 'Calendar' [Year]、 'Calendar' [Year] +1）
- 会計月
IF（ 'Calendar' [Month] ⇐ 'Calendar' [FYE]、12-'Calendar '[FYE] +' Calendar '[Month]、' Calendar '[Month]-' Calendar '[FYE]）
- 会計四半期
INT（（ 'Calendar' [会計月] +2）/3）
日付テーブルプロパティの設定
TOTALYTD、PREVIOUSMONTH、DATESBETWEENなどのDAXタイムインテリジェンス関数を使用する場合、正しく機能するにはメタデータが必要です。 日付テーブルプロパティは、このようなメタデータを設定します。
日付テーブルのプロパティを設定するには-
- Power Pivotウィンドウで[カレンダー]テーブルを選択します。
- リボンの[デザイン]タブをクリックします。
- [カレンダー]グループの[日付テーブルとしてマーク]をクリックします。
- ドロップダウンリストで[日付テーブルとしてマーク]をクリックします。
[日付テーブルとしてマーク]ダイアログボックスが表示されます。 [カレンダー]テーブルの[日付]列を選択します。 これは日付データ型の列である必要があり、一意の値を持っている必要があります。 OKをクリックしてください。
データモデルの拡張
この章では、前の章で作成したデータモデルを拡張する方法を学習します。 データモデルの拡張には以下が含まれます-
- テーブルの追加
- 既存のテーブルに計算列を追加する
- 既存のテーブルでのメジャーの作成
これらのうち、メジャーを作成することは非常に重要です。これは、データモデルを使用するユーザーがやり直しを避け、データと意思決定の分析中に時間を節約できるようにするデータモデルに新しいデータの洞察を提供することを伴うためです
損益分析には期間の操作が含まれ、DAXタイムインテリジェンス関数を使用するため、データモデルに日付テーブルが必要です。
日付テーブルを初めて使用する場合は、「日付テーブルについて」の章をご覧ください。
次のようにデータモデルを拡張することができます-
- データテーブル間にリレーションシップを作成するには、つまり 財務データテーブルと日付テーブル、財務データテーブルに計算列日付を作成する必要があります。
- さまざまなタイプの計算を実行するには、データテーブル-Finance Dataとルックアップテーブル-Accounts and Geography Locnの間に関係を作成する必要があります。
- いくつかの計算を実行し、必要な分析を実行するのに役立つさまざまなメジャーを作成する必要があります。
これらの手順は、基本的に、データモデルを使用した損益分析のデータモデリング手順を構成します。 ただし、これは、Power Pivotデータモデルで実行する任意の種類のデータ分析の一連の手順です。
さらに、以降の章のパワーピボットテーブルでメジャーの作成方法と使用方法を学習します。 これにより、DAXを使用したデータモデリングとPower PivotTablesを使用したデータ分析について十分に理解できます。
データモデルへの日付テーブルの追加
次のように会計年度にまたがる期間の日付テーブルを作成します-
- 新しいExcelワークシートで、ヘッダーを持つ日付と連続した日付（2011年7月1日から2018年6月30日まで）を持つ単一の列を持つテーブルを作成します。
- Excelからテーブルをコピーし、Power Pivotウィンドウに貼り付けます。 これにより、Power Pivotデータモデルに新しいテーブルが作成されます。
- テーブルにDateという名前を付けます。
- 日付テーブルの日付列のデータ型が日付（DateTime）であることを確認します。
次に、計算された列を追加する必要があります-会計年度、会計四半期、会計月、月を次のように日付テーブルに追加します-
会計年度
会計年度末が6月30日であるとします^^。 次に、会計年度は7月1日から6月30日までです。 たとえば、2011年7月1日（2011年7月1日）から2012年6月30日（2012年6月30日）までの期間は2012会計年度になります。
日付テーブルで、2012年度と同じものを表示するとします。
- 最初に日付の会計年度部分を抽出し、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式を使用して、Dateテーブルに計算列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式を使用して、Dateテーブルに計算列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式を使用して、日付テーブルに計算列の会計月を追加します- + _ = 'Date' [会計年度]＆ "-P"＆FORMAT（MOD（MONTH（[Date]）+ [FYE] -1,12）+1、 "00"）_
月
最後に、次のように会計年度の月番号を表す計算列月を追加します-
_ = FORMAT（MOD（MONTH（[Date]）+ [FYE] -1,12）+1、 "00"）＆ "-"＆FORMAT（[Date]、 "mmm"）_
結果の日付テーブルは、次のスクリーンショットのようになります。
次のスクリーンショットに示すように、テーブルに日付を列の日付テーブルとしてマークし、日付を一意の値の列としてマークします。
計算列の追加
Finance DataテーブルとDateテーブルの間にリレーションシップを作成するには、Finance Dataテーブルに日付値の列が必要です。
- DAX式を使用して、財務データテーブルに計算列の日付を追加します- + _ = DATEVALUE（ '金融データ' [会計月]）_
データモデルのテーブル間の関係の定義
データモデルには次の表があります-
- データ表-財務データ
- ルックアップテーブル-アカウントと地理Locn
- 日付表-日付
データモデルのテーブル間の関係を定義するには、次の手順があります-
- Power Pivotのダイアグラムビューで表を表示します。
- テーブル間に次の関係を作成します-
- 財務データテーブルと列テーブルを持つアカウントテーブルの関係。
- Finance DataテーブルとGeography LocnテーブルとProfit Center列の関係。
- 財務データテーブルと、日付列を含む日付テーブルの関係。
クライアントツールから列を非表示にする
データテーブルに、ピボットテーブルのフィールドとして使用しない列がある場合、データモデルで非表示にできます。 そうすると、それらはピボットテーブルフィールドリストに表示されなくなります。
[財務データ]テーブルには、ピボットテーブルのフィールドとして使用しない、会計月、日付、アカウント、および利益センターの4つの列があります。 したがって、それらを非表示にして、ピボットテーブルフィールドリストに表示されないようにすることができます。
- [財務データ]テーブルの列-[会計月]、[日付]、[アカウント]、および[利益センタ]を選択します。
- 右クリックして、ドロップダウンリストで[クライアントツールから非表示]を選択します。
テーブルでメジャーを作成する
データモデルとPower PivotTablesを使用して、DAXを使用したデータモデリングと分析の準備がすべて整いました。
後続の章では、メジャーを作成する方法と、PowerPivotTablesでメジャーを使用する方法を学習します。 データテーブルにすべてのメジャーを作成します。 財務データ表。
データテーブルのDAX数式を使用してメジャーを作成します-Finance Data。これは、データ分析用の任意の数のピボットテーブルで使用できます。 メジャーは本質的にメタデータです。 データテーブルでメジャーを作成することはデータモデリングの一部であり、それらをPower PivotTablesで要約することはデータ分析の一部です。
ベースファイナンスの測定と分析
データモデルにさまざまなメジャーを作成して、任意の数のパワーピボットテーブルで使用できます。 これにより、DAXを使用したデータモデルとのデータモデリングおよび分析プロセスが形成されます。
前のセクションで以前に学習したように、データのモデリングと分析は特定のビジネスとコンテキストに依存しています。 この章では、サンプルの損益データベースに基づいてデータのモデリングと分析を学習し、必要なメジャーを作成し、それらをさまざまなPower PivotTablesで使用する方法を理解します。
あらゆるビジネスとコンテキストのデータモデリングと分析に同じ方法を適用できます
財務データに基づくメジャーの作成
財務レポートを作成するには、特定の期間、組織、アカウント、または地理的な場所の金額を計算する必要があります。 また、人数と人数ごとの計算を実行する必要があります。 データモデルでは、他のメジャーの作成で再利用できるベースメジャーを作成できます。 これは、DAXを使用したデータモデリングの効果的な方法です。
損益データ分析のための計算を実行するために、合計、前年比、年間累計、四半期累計、差異、人員、人員単価などの指標を作成できます。 Powerピボットテーブルでこれらのメジャーを使用して、データを分析し、分析結果をレポートできます。
以下のセクションでは、ベースファイナンスメジャーを作成し、それらのメジャーでデータを分析する方法を学習します。 メジャーは、他の財務メジャーの作成に使用できるため、ベースメジャーと呼ばれます。 また、以前の期間のメジャーを作成し、分析で使用する方法も学習します。
基本財務指標の作成
財務データ分析では、予算と予測が重要な役割を果たします。
予算
予算は、会計年度の会社の収入と支出の見積もりです。 予算は会計年度の初めに計算され、会社の目標と目標が考慮されます。 市場の状況が変化する可能性があり、会社がその目標と目標を業界の現在の傾向に合わせる必要があるため、会計年度中に予算措置を分析する必要があります。
予測
財務予測は、企業の収益と費用の履歴データを調べることにより、企業の将来の財務結果を予測するものです。 あなたは次の財務予測を使用することができます-
- 将来の期間に予算を割り当てる方法を決定します。
- 会社の期待されるパフォーマンスを追跡するため。
- 目標に対する不足に対処するため、または新たな機会を最大化するために、タイムリーな決定を下すため。
実績
予算と予測の計算を実行するには、任意の時点で実際の収益と費用が必要です。
あなたは、データモードで他の財務指標を作成する際に使用できる次の3つのベース財務指標を作成することができます-
- 予算額
- 実際の合計
- 予測合計
これらの測定値は、[財務データ]テーブルの列（予算、実績、および予測）の集計合計です。
次のように基本的な財務指標を作成します-
予算合計
Budget Sum：= SUM（ 'Finance Data' [Budget]）
実際の合計
Actual Sum：= SUM（ 'Finance Data' [Actual]）
予測合計
Forecast Sum：= SUM（ 'Finance Data' [Forecast]）
基本財務指標を使用したデータの分析
基本財務指標と日付テーブルを使用すると、次のように分析を実行できます-
- Powerピボットテーブルを作成します。
- DateテーブルのFiscal YearフィールドをRowsに追加します。
- [ピボットテーブルフィールド]リストのフィールドとして表示されるメジャーの[予算合計]、[実際の合計]、および[予測合計]を[値]に追加します。
前期間の財務指標の作成
3つの基本財務指標と日付テーブルを使用して、他の財務指標を作成できます。
四半期の実績合計を前の四半期の実績合計と比較するとします。 メジャー-前四半期実績合計を作成できます。
前四半期実績合計：= CALCULATE（[実績合計]、DATEADD（ 'Date' [日付]、1、QUARTER））
同様に、メジャー-前年実績合計を作成できます。
前年の実際の合計：= CALCULATE（[実際の合計]、DATEADD（ 'Date' [日付]、1、YEAR））
前期間の財務指標を使用したデータの分析
基本メジャー、前期間のメジャーおよび日付テーブルを使用すると、次のように分析を実行できます-
- Powerピボットテーブルを作成します。
- 日付テーブルの「会計四半期」フィールドを「行」に追加します。
- メジャーの実績合計と前四半期の実績合計を値に追加します。
- 別のPower PivotTableを作成します。
- DateテーブルのFiscal YearフィールドをRowsに追加します。
- メジャーの実績合計と前年の実績合計を値に追加します。
前年比の財務指標と分析
前年比（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関数は、行のAccountsテーブルの列クラスに「純収益」がある場合、TRUEを返します。
- DAX IF関数は–（[Actual Sum]-[Preyear Year Actual Sum]）を返します。
- それ以外の場合、DAX IF関数は[Actual Sum]-[Previous Year Actual Sum]を返します。
前年比の測定値の作成
あなたは年率をパーセントで表すことができます-
*_（YoY）/（前年の実績合計）_*あなたは、次のDAXの式で前年比パーセントメジャーを作成することができます-
YoY％：= IF（[前年実績合計]、[前年]/ABS（[前年実績合計]）、BLANK（））
上記の式でDAX IF関数を使用して、ゼロによる除算がないことを確認します。
前年比でデータを分析する
次のようにパワーピボットテーブルを作成します-
- [アカウント]テーブルの[クラス]および[サブクラス]フィールドを[行]に追加します。
- メジャー（実績合計、前年実績合計、YoY、YoY％）を値に追加します。
- DateテーブルのFiscal Yearフィールドにスライサーを挿入します。
- スライサーで2016年度を選択します。
予算の前年比測定の作成
次のように、前年比予算を作成できます-
予算の前年比：= IF（CONTAINS（Accounts、Accounts [Class]、 "Net Revenue"）、 - （[予算合計]-[前年の実績合計]）、 [Budget Sum] - [Prior Year Actual Sum])
予算の前年比測定値の作成
次のように、予算の前年比を作成できます-
予算の前年比％：= IF（[前年の実績合計]、[予算の前年比]/ABS（[前年の実績合計]）、BLANK（））
予算の前年比測定によるデータの分析
次のようにパワーピボットテーブルを作成します-
- [アカウント]テーブルの[クラス]および[サブクラス]フィールドを[行]に追加します。
- メジャー-予算合計、前年度実績合計、予算YoYおよび予算YoY％を値に追加します。
- DateテーブルのFiscal Yearフィールドにスライサーを挿入します。
- スライサーで2016年度を選択します。
前年比予測の作成
あなたは次のように予測年間前年メジャーを作成することができます-
Forecast YoY：= IF（CONTAINS（Accounts、Accounts [Class]、 "Net Revenue"）、 - （[予測額]-[前年実績額]）、 [Forecast Sum] - [Prior Year Actual Sum])
前年比予測の測定値の作成
次のように予測年間前年比を作成することができます-
予測前年比％：= IF（[前年実績合計]、[予測前年比]/ABS（[前年実績合計]）、BLANK（））
前年比予測によるデータの分析
次のようにパワーピボットテーブルを作成します-
- [アカウント]テーブルの[クラス]および[サブクラス]フィールドを[行]に追加します。
- メジャーを追加します-予測合計、前年実績合計、予測YoYおよび予測YoY％を値に。
- データテーブルの会計年度フィールドにスライサーを挿入します。
- スライサーで2016年度を選択します。
分散測定と分析
予算への差異、予測への差異、予算への予測差異などの差異測定を作成できます。 これらの指標に基づいて財務データを分析することもできます。
予算合計メジャーに対する分散の作成
次のように予算合計メジャーへの分散（VTB合計）を作成します-
_VTB合計：= [予算合計]-[実際の合計] _
予算割合メジャーに対する分散の作成
次のように予算の割合の測定値（VTB％）の分散を作成します-
VTB％：= IF（[予算合計]、[VTB合計]/ABS（[予算合計]）、BLANK（））
予算メジャーに対する分散を伴うデータの分析
次のようにパワーピボットテーブルを作成します-
- 日付テーブルから会計年度を行に追加します。
- [財務データ]テーブルの[実績合計]、[予算合計]、[VTB合計]、[VTB％]のメジャーを[値]に追加します。
予測の合計メジャーに対する分散の作成
次のように、Variance to Forecast Sum（VTF Sum）メジャーを作成します-
_VTF Sum：= [予測合計]-[実際の合計] _
分散を予測するためのパーセンテージ測定
次のように、予測の変化に対する分散測定（VTF％）を作成します-
VTF％：= IF（[予測合計]、[VTF合計]/ABS（[予測合計]）、BLANK（））
予測メジャーに対する分散を使用したデータの分析
次のようにパワーピボットテーブルを作成します-
- 日付テーブルから会計年度を行に追加します。
- [財務データ]テーブルのメジャー[実績合計]、[予測合計]、[VTF合計]、[VTF％]を[値]に追加します。
予算合計メジャーに対する予測分散の作成
次のように、予算の合計に対する予測分散（予測VTBの合計）メジャーを作成します-
_予測VTB合計：= [予算合計]-[予測合計] _
予算割合メジャーに対する予測差異の作成
次のように予算の割合（予測VTBの割合）メジャーに対する予測の差異を作成します-
予測VTB％：= IF（[予算合計]、[予測VTB合計]/ABS（[予算合計]）、BLANK（））
予算メジャーに対する予測分散を使用したデータの分析
次のようにパワーピボットテーブルを作成します-
- 日付テーブルから会計年度を行に追加します。
- [財務データ]テーブルのメジャー[予算合計]、[予測合計]、[予測VTB合計]、[予測VTB％]を[値]に追加します。
年初からの測定と分析
会計年度などの期間の開始から特定の期間までの開始残高を含む結果を計算するには、DAXタイムインテリジェンス関数を使用できます。 これにより、月レベルでデータを分析できます。
この章では、年初来のメジャーを作成する方法と、それを使用してデータ分析を実行する方法を学習します。
年初来の実績合計メジャーの作成
次のように年初来の実績合計メジャーを作成します-
YTD Actual Sum：= TOTALYTD（[Actual Sum]、 'Date' [Date]、ALL（ 'Date'）、 "6/30"）
年次予算合計メジャーの作成
次のように年累計予算合計メジャーを作成します-
YTD Budget Sum：= TOTALYTD（[Budget Sum]、 'Date' [Date]、ALL（ 'Date'）、 "6/30"）
年初来の予測合計メジャーの作成
次のように年累計の予測合計メジャーを作成します-
YTD Forecast Sum：= TOTALYTD（[Forecast Sum]、 'Date' [Date]、ALL（ 'Date'）、 "6/30"）
前年度累計実績合計メジャーの作成
次のように、前年度累計実績合計メジャーを作成します-
前年YTD実績合計：= TOTALYTD（[前年実績合計]、 '日付' [日付]、ALL（ '日付'）、 "6/30"）
年初来のメジャーでデータを分析する
次のようにパワーピボットテーブルを作成します-
- 日付テーブルから行に月を追加します。
- 財務データテーブルからメジャーの実績合計、YTD実績合計、YTD予算合計、YTD予測合計を値に追加します。
- 日付テーブルから会計年度にスライサーを挿入します。
- スライサーで2016年度を選択します。
次のようにパワーピボットテーブルを作成します-
- 日付テーブルから行に月を追加します。
- [財務データ]テーブルの[実績合計]、[YTD実績合計]、[前年実績実績]、および[前年YTD実績合計]メジャーを[値]に追加します。
- 日付テーブルから会計年度にスライサーを挿入します。
- スライサーで2016年度を選択します。
四半期累計の測定と分析
会計四半期などの期間の開始から特定の期間までの開始残高を含む結果を計算するには、DAXタイムインテリジェンス関数を使用できます。 これにより、月レベルでデータを分析できます。
この章では、四半期累計メジャーを作成する方法と、それを使用してデータ分析を実行する方法を学習します。
四半期累計メジャーの作成
次のように四半期累計実績合計メジャーを作成します-
QTD Actual Sum：= TOTALQTD（[Actual Sum]、 'Date' [Date]、ALL（ 'Date'））
四半期累計の予算合計メジャーの作成
次のように四半期累計予算合計メジャーを作成します-
QTD Budget Sum：= TOTALQTD（[Budget Sum]、 'Date' [Date]、ALL（ 'Date'））
四半期累計予測合計メジャーの作成
次のように四半期累計予算合計メジャーを作成します-
QTD Budget Sum：= TOTALQTD（[Budget Sum]、 'Date' [Date]、ALL（ 'Date'））
四半期累計予測合計メジャーの作成
次のように四半期累計の予測合計メジャーを作成します-
QTD Forecast Sum：= TOTALQTD（[Forecast Sum]、 'Date' [Date]、ALL（ 'Date'））
前四半期累計実績合計メジャーの作成
次のように前四半期累計実績合計メジャーを作成します-
Prior QTD Actual Sum：= TOTALQTD（[Prior Quarter Actual Sum]、 'Date' [Date]、ALL（ 'Date'））
四半期累計メジャーを使用したデータの分析
次のようにパワーピボットテーブルを作成します-
- 日付テーブルから行に会計月を追加します。
- 財務データテーブルからの実績合計、QTD実績合計、QTD予算合計、およびQTD予測合計を値に追加します。
- 日付テーブルから会計四半期にスライサーを挿入します。
- スライサーでFY2016-Q2を選択します。
次のようにパワーピボットテーブルを作成します-
- 日付テーブルから行に会計月を追加します。
- 財務データテーブルからのメジャー実績合計、QTD実績合計、前四半期実績合計、および前QTD実績合計を値に追加します。
- 日付テーブルから会計四半期にスライサーを挿入します。
- スライサーでFY2016-Q1を選択します。
予算の測定と分析
予算編成には、会計年度にわたる企業のキャッシュフローの推定が含まれます。 会社の財政状態、その目標、予想される収益、および費用は、予算編成で考慮されます。
ただし、会計年度中に市場の状況が変化する可能性があり、会社は目標をリセットする必要があります。 これには、会計年度の初めに見積もられた予算（予算合計）と会計年度の初めから現在までの実際の支出合計（YTD実績合計）で財務データを分析する必要があります。
会計年度中のいつでも、あなたは以下を計算することができます-
予想外の残高
未支出残高は、実際の費用の後に残っている予算です。
*_未収残高= YTD予算合計– YTD実績合計_*予算達成率
予算達成率は、これまでに費やした予算の割合です。
*_予算達成率= YTD実績合計/YTD予算合計_*これらの計算は、予算を使用して意思決定を行う企業に役立ちます。
予期しないバランス測定の作成
次のように未支出のバランスの尺度を作成できます-
_Unexpended Balance：= CALCULATE（ [YTD Budget Sum],ALL('Finance Data'[Date]) ）-[YTD Actual Sum] _
予算達成率メジャーの作成
次のように予算達成率の指標を作成できます-
予算達成率％：= IF（[YTD予算合計]、[YTD実績合計]/CALCULATE（[YTD予算合計]、ALL（ '金融データ' [日付]））、BLANK（））
予算メジャーを使用したデータの分析
次のようにパワーピボットテーブルを作成します-
- DateテーブルのMonthをRowsに追加します。
- メジャー「予算合計」、「YTD予算合計」、「YTD実績合計」、「予算達成率」、および「財務データテーブルからの未支出残高」を「値」に追加します。
- 「会計年度」フィールドにスライサーを挿入します。
- スライサーで2016年度を選択します。
予測測定と分析
予測指標を使用して財務データを分析し、組織が年間の目標と目標に必要な調整を行い、変化するビジネス要件に合わせて会社のパフォーマンスを調整できるようにします。
変更に対応するには、予測を定期的に更新する必要があります。 その後、最新の予測を会計年度の残りの期間の予算と比較して、会社がビジネスの変化に対応するために必要な調整を行えるようにします。
会計年度中のいつでも、あなたは以下を計算することができます-
予測達成率
予測達成率は、これまでに費やした予測合計の割合です。
*_予測達成率= YTD実績合計/YTD予測合計_*予想外の残高
予測未払い残高は、実際の費用の後に残っている予測合計、つまり
*_予測未収残高= YTD予測合計– YTD実績合計_*予算調整
予算調整とは、予測に基づいて組織が行う必要がある予算額の調整（増減）です。
*_予算調整=予想外の残高-予想外の残高_*結果の値が正の場合、予算を増やす必要があります。 それ以外の場合は、他の目的に合わせて調整できます。
予測到達率メジャーの作成
次のように、予測到達率メジャーを作成できます-
予測到達率：= IF（[YTD予測合計]、[YTD実績合計]/[YTD予測合計]、BLANK（））
予測未計画バランス測定の作成
次のように予測未収バランス指標を作成できます-
_予測されていない残高：= [YTD予測合計]-[YTD実績合計] _
予算調整メジャーの作成
次のように予算調整指標を作成できます-
_予算調整：= [予想外の残高]-[予想外の残高] _
予測メジャーを使用したデータの分析
次のようにパワーピボットテーブルを作成します-
- 日付テーブルから行に月を追加します。
- メジャー「予算合計」、「YTD予算合計」、「YTD実績合計」、「予算達成率」、および「財務データテーブルからの未支出残高」を「値」に追加します。
- 会計年度にスライサーを挿入します。
- スライサーで2016年度を選択します。
月数の測定
ヘッドカウントメジャーとヘッドあたりコストメジャーの作成に使用できる月数カウントメジャーを作成できます。 これらのメジャーは、Financial DataテーブルのActual列/Budget列/Forecast列にゼロ以外の値があるFiscal Month列の個別の値をカウントします。 これが必要なのは、Finance DataテーブルのActual列にゼロ値が含まれており、それらの行がヘッドカウントとヘッドごとのコストの計算中に除外されるためです。
実際の月数のカウントの作成
次のように、実際の月数のカウントを作成できます-
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)
終了人数の測定
特定の期間の終了人員メジャーを作成できます。 Ending Headcountは、指定された期間の最後の日付における人の合計であり、その期間には非空白の人がいます。
終了ヘッドカウントは次のように取得されます-
- 月間-特定の月の終わりの人の合計。
- 四半期の場合-特定の四半期の最終月の終わりの人の合計。
- For a Year-特定の年の最後の月の終わりの人の合計。
実際の終了人員メジャーの作成
次のように実際の終了ヘッドカウントメジャーを作成することができます-
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））
前年度の実際の終了人員メジャーの作成
次のように、前年度の実際の終了人員メジャーを作成できます-
前年の実際の終了ヘッドカウント：= CALCULATE（ 'Finance Data' [Actual Ending Head Count]、DATEADD（ 'Date' [Date]、-1、YEAR））
終了人員メジャーを使用したデータの分析
次のようにパワーピボットテーブルを作成します-
- 日付テーブルの「会計年度」および「月」フィールドを「行」に追加します。
- 財務データテーブルからの値に、実際の終了ヘッドカウント、予算の終了ヘッドカウント、予測の終了ヘッドカウント、前年の実際の終了ヘッドカウントを追加します。
- 「会計年度」フィールドにスライサーを挿入します。
- スライサーで2016年度を選択します。
平均人数カウント
前の章では、特定の期間の終了人数を計算する方法を学びました。 同様に、任意の月を選択して平均月間人数を作成できます。
平均月間人数は、選択した月の数で割った月間人数の合計です。
DAX AVERAGEX関数を使用して、これらのメジャーを作成できます。
実際の平均人員メジャーの作成
次のように実際の平均人数カウントを作成できます-
実際の平均人員数：= AVERAGEX（VALUES（ '財務データ' [会計月]）、[実際の終了人員数]）
予算平均人員メジャーの作成
次のように実際の平均人数カウントを作成できます-
平均予算人数：= AVERAGEX（VALUES（ '財務データ' [会計月]）、[予算終了人数]）
予測平均人員メジャーの作成
次のように予測平均人員メジャーを作成できます-
予測平均人数：= AVERAGEX（VALUES（ '財務データ' [会計月]）、[実際の終了人数]）
前年度の実際の平均人員メジャーの作成
次のように、前年度の実際の平均人数のメジャーを作成できます-
前年の実際の平均人員数：= CALCULATE（ '金融データ' [実際の平均人員数]、DATEADD（ '日付' [日付]、-1、YEAR））
平均人数カウントによるデータの分析
次のようにパワーピボットテーブルを作成します-
- 日付テーブルの「会計年度」および「月」フィールドを「行」に追加します。
- メジャーの平均平均人員、予算の平均人員、予測の平均人員、前年度の実際の平均人員を財務データテーブルから値に追加します。
- 「会計年度」フィールドにスライサーを挿入します。
- スライサーで2016年度を選択します。
総人数測定
前の章では、月数メジャーと平均人数カウントメジャーを作成する方法を学びました。 これらの指標を使用して、基本的な人数の指標を計算できます-
- 実際の総人数
- 予算合計人員 *総従業員数の予測
後続の章では、YoYヘッドカウントや分散メジャーなどの他の計算でこれらのベースヘッドカウントメジャーを使用する方法を学習します。
実際の合計人員メジャーの作成
次のように、実際の合計人員メジャーを作成できます-
_実際の総ヘッド数：= 'ファイナンスデータ' [実際の平均ヘッドカウント]* 'ファイナンスデータ' [CountOfActualMonths] _
予算合計人員メジャーの作成
次のように予算合計人員メジャーを作成できます-
_予算合計人員数：= '財務データ' [予算平均人員数] *'財務データ' [CountOfBudgetMonths] _
予測合計人員メジャーの作成
次のように予測総従業員数を作成できます-
_予測総従業員数：= '財務データ' [予測平均従業員数]* '財務データ' [CountOfForecastMonths] _
前年比の人員測定と分析
前の章では、基本的な人数のメジャーを作成する方法を学びました。 実際の合計人員、予算の合計人員、および予測の合計人員。
この章では、前年比の人員メジャーを作成する方法と、これらのメジャーを使用してデータを分析する方法を学習します。
前年同期の実際の終了人員メジャーの作成
次のように、前年同期の実際の終了人員メジャーを作成できます-
_YoY実際の終了ヘッドカウント：= [実際の終了ヘッドカウント]-[前年の実際の終了ヘッドカウント] _
前年の実際の平均人員メジャーの作成
次のように、前年比の実際の平均人員メジャーを作成できます-
_前年の実際の平均人数：= [実際の平均人数]-[前年の実際の平均人数] _
前年の実際の総従業員数メジャーの作成
次のように、前年比の実際の合計人員メジャーを作成できます-
_YoY実際の合計人数：= [実際の合計人数]-[前年の実際の合計人数] _
前年の実際の人員メジャーでデータを分析する
次のようにパワーピボットテーブルを作成します-
- 日付テーブルの「会計四半期」および「月」フィールドを「行」に追加します。
- メジャー-実際の終了ヘッド数、前年の実際の終了ヘッド数、前年比の実際の終了ヘッド数を値に追加します。
- フィールド「会計年度」にスライサーを挿入します。
- スライサーで2016年度を選択します。
次のように、同じワークシートに別のPower PivotTableを作成します-
- 日付テーブルの「会計四半期」および「月」フィールドを「行」に追加します。
- メジャーを追加-実際の平均ヘッドカウント、前年の実際の平均ヘッドカウント、YoYの実際の平均ヘッドカウントを値に追加します。
次のようにこのピボットテーブルにスライサーを接続します-
- スライサーをクリックします。
- リボンのスライサーツールの下にある[オプション]タブをクリックします。
- [接続のレポート]をクリックします。
[レポート接続]ダイアログボックスが表示されます。
- 上記の2つのピボットテーブルを選択します。
- OKをクリックしてください。
前年比予算終了人員メジャーの作成
次のように、前年比予算終了人員メジャーを作成できます-
_YoY予算の終了人数：= [予算の終了人数]-[前年の実際の終了人数] _
前年の予算平均人員メジャーの作成
次のように、年間予算平均人員メジャーを作成できます-
_前年予算平均人員：= [予算平均人員]-[前年の実際の平均人員] _
前年の予算合計人員メジャーの作成
次のように、年間予算合計人員メジャーを作成できます-
_YoY予算合計人員数：= [予算合計人員数]-[前年の実際の合計人員数] _
前年同期の終了人員メジャーの作成
次のように、前年同期の終了人員メジャーを作成できます-
_YoY予測終了ヘッドカウント：= [予測終了ヘッドカウント]-[前年の実際の終了ヘッドカウント] _
前年同期の平均人員メジャーの作成
次のように、前年同期の平均人員メジャーを作成できます-
_YoY予測平均人員：= [予測平均人員]-[前年の実際の平均人員] _
前年比の総従業員数メジャーの作成
次のように、前年同期の総人員メジャーを作成できます-
_YoY予測総従業員数：= [予測総従業員数]-[前年の実際の総従業員数] _
分散人員測定
これまでに作成したヘッドカウントメジャーに基づいて、分散ヘッドカウントメジャーを作成できます。
予算終了人員メジャーへの分散の作成
次のように、予算終了人員メジャーへの分散を作成できます-
_VTB終了ヘッドカウント：= 'ファイナンスデータ' [予算終了ヘッドカウント]-'ファイナンスデータ' [実際の終了ヘッドカウント] _
予算平均人員メジャーへの分散の作成
あなたは次のように予算平均人員メジャーへの分散を作成することができます-
VTB平均ヘッドカウント：= 'ファイナンスデータ' [予算平均ヘッドカウント]-'ファイナンスデータ' [実際の平均ヘッドカウント
予算合計人員メジャーへの分散の作成
次のように予算合計人員メジャーへの分散を作成できます-
_VTB総ヘッド数：= 'ファイナンスデータ' [予算合計ヘッド数]-'ファイナンスデータ' [実際の合計ヘッド数] _
終了人員メジャーを予測するための分散の作成
次のように予測終了人員メジャーへの分散を作成できます-
_VTF終了ヘッドカウント：= 'ファイナンスデータ' [予測終了ヘッドカウント]-'ファイナンスデータ' [実際の終了ヘッドカウント] _
平均人員メジャーを予測するための分散の作成
次のように、平均人員メジャーを予測するための分散を作成できます-
_VTF平均ヘッドカウント：= 'ファイナンスデータ' [予測平均ヘッドカウント]-'ファイナンスデータ' [実際の平均ヘッドカウント] _
合計人員メジャーを予測するための分散の作成
次のように、合計人員メジャーを予測するための分散を作成できます-
_VTF総ヘッドカウント：= 'ファイナンスデータ' [予測総ヘッドカウント]-'ファイナンスデータ' [実際の総ヘッドカウント] _
予算終了人員メジャーに対する予測差異の作成
次のように予算終了人員メジャーへの予測の差異を作成できます-
_予測VTB終了ヘッドカウント：= 'ファイナンスデータ' [予算終了ヘッドカウント]-'ファイナンスデータ' [予測終了ヘッドカウント] _
予算平均人員メジャーに対する予測分散の作成
次のように予算平均人員メジャーへの予測分散を作成できます-
_予測VTB平均人数：= 'ファイナンスデータ' [予算平均人数]-'ファイナンスデータ' [予測平均人数] _
予算合計人員メジャーに対する予測差異の作成
次のように予算合計人員メジャーへの予測分散を作成できます-
予測VTB合計ヘッドカウント：= 'ファイナンスデータ' [予算合計ヘッドカウント]-'ファイナンスデータ' [予測合計ヘッドカウント
従業員あたりのコストの測定と分析
メジャーの2つの主要なカテゴリについて学習しました-
- 財務指標。 *人数の測定。
学習するメジャーの3番目の主要なカテゴリは、人件費メジャーです。 どの組織も、1人あたりの年間コストを知りたいと思うでしょう。 1人あたりの年間コストは、1人の従業員を1年間雇用することによる会社のコストを表します。
人件費メジャーを作成するには、最初に特定の予備の人件費メジャーを作成する必要があります。 Accountsテーブルには、値の1つとしてPeopleを含むサブクラス列があります。 したがって、[サブクラス]列の[アカウント]テーブルにフィルターを適用して、人件費を取得するために[金融データ]テーブルにフィルターコンテキストを取得できます。
したがって、人件費メジャーと月数メジャーを取得して、年間人件費メジャーを作成できます。 最終的に、年間人件費メジャーおよび平均人数カウントメジャーから、頭部当たりの年間コストメジャーを作成できます。
実際の人件費メジャーの作成
次のように実際の人件費メジャーを作成できます-
実際の人件費：= CALCULATE（ 'Finance Data' [Actual Sum]、FILTER（ 'Finance Data'、RELATED（Accounts [Sub Class]）= "People"））
予算人件費メジャーの作成
次のように予算人件費メジャーを作成できます-
予算の人件費：= CALCULATE（ 'Finance Data' [Budget Sum]、FILTER（ 'Finance Data'、RELATED（Accounts [Sub Class]）= "People"））
予測人件費メジャーの作成
次のように予測人件費メジャーを作成できます-
予測人件費：= CALCULATE（ 'Finance Data' [Forecast Sum]、FILTER（ 'Finance Data'、RELATED（Accounts [Sub Class]）= "People"））
年換算の実際の人件費メジャーの作成
次のように年間実績人件費メジャーを作成できます-
年間の実際の人件費：= IF（[CountOfActualMonths]、[Actual People Cost]* 12/[CountOfActualMonths]、BLANK（））
年間予算人件費メジャーの作成
次のように年次予算人件費メジャーを作成できます-
年次予算人件費：= IF（[CountOfBudgetMonths]、 [Budget People Cost]*12/[CountOfBudgetMonths],BLANK())
年間予測人件費メジャーの作成
次のように年間予測人件費メジャーを作成できます-
年間予測人件費：= IF（[ForecastMonthsのカウント]、[予測人件費] * 12/[ForecastMonthsのカウント]、BLANK（））
ヘッドメジャーごとの実際の年間コストの作成
次のように、実際の年間頭当たりコスト（CPH）メジャーを作成できます-
実際の年間CPH：= IF（[実際の平均人員]、[年間の実際の人件費]/[実際の平均人員]、BLANK（））
1人あたりの予算年間コストの作成
次のように、1人あたりの予算年間コスト（CPH）メジャーを作成できます-
予算年換算CPH：= IF（[予算平均人員]、[年間予算人件費]/[予算平均人員]、BLANK（））
予測の年間ヘッドコストあたりのコストの作成
次のように予測年間年間費用（CPH）メジャーを作成できます-
予測年間CPH：= IF（[予測平均人員]、[年間予測人件費]/[予測平均人員]、BLANK（））
前年度の実際の年間平均コスト/ヘッド測定の作成
次のように、前年度の実際の年間頭当たりコスト（CPH）メジャーを作成できます-
Prior Year Actual Annualized CPH：= CALCULATE（[Actual Annualized CPH]、DATEADD（ 'Date' [Date]、-1、YEAR））
コスト単価でデータを分析する
次のようにパワーピボットテーブルを作成します-
- 日付テーブルからの会計四半期と会計月のフィールドを行に追加します。
- 実際の年次CPH、予算年次CPH、および予測年次CPHのメジャーを列に追加します。
- [日付]テーブルの[会計年度]フィールドを[フィルター]に追加します。
- フィルターで2016年度を選択します。
次のように別のPower PivotTableを作成します-
- 日付テーブルの「会計四半期」フィールドを「行」に追加します。
- メジャーの実際の年間CPHと前年の実際の年間CPHを列に追加します。
- DateテーブルのFiscal Yearフィールドにスライサーを挿入します。
- スライサーで2015年度と2016年度を選択します。
レート分散とボリューム分散
年間一人当たりコストと総人員のメジャーを作成する方法を学習しました。 これらのメジャーを使用して、レート分散メジャーとボリューム分散メジャーを作成できます。
- レート変動測定では、1人あたりのコストの違いによって通貨変動のどの部分が生じるかを計算します。
- ボリューム差異測定は、人員の変動によってどれだけの通貨差異が生じるかを計算します。
予算レート測定に対する分散の作成
次のように、予算レートへの分散メジャーを作成できます-
_VTBレート：=（[予算年間CPH]/12- [実際の年間CPH]/12） *[実際の総人数] _
予算量測定に対する分散の作成
あなたは次のように予算量の測定への分散を作成することができます-
VTB Volume：= [VTB Total Head Count]* [Budget Annualized CPH]/12
予算メジャーに対する分散を伴うデータの分析
次のようにパワーピボットテーブルを作成します-
- 日付テーブルからの会計四半期と会計月のフィールドを行に追加します。
- 実績年次CPH、予算年次CPH、VTBレート、VTBボリューム、VTB合計のメジャーを値に追加します。
- 日付テーブルの会計年度フィールドとアカウントテーブルのサブクラスをフィルターに追加します。
- 会計年度フィルターで2016年度を選択します。
- サブクラスフィルターで「人」を選択します。
- 2016年度第1四半期および2016年度第2四半期の値のフィルター行ラベル。
あなたは上記のピボットテーブルで次を観察することができます-
- 表示されるVTB合計値は、サブクラス-人のみのものです。
- 2016年度第1四半期のVTB合計は4,705,568ドル、VTBレートは970,506,297ドル、VTBボリュームは-965,800,727ドルです。
- VTBレートの測定では、予算との差異（VTB合計）の970,506,297ドルは1人あたりのコストの差によって生じ、$-965,800,727はヘッドカウントの差によって生じます。
- VTBレートとVTBボリュームを追加すると、サブクラスの人のVTB合計で返される値と同じ4,705,568ドルが得られます。
- 同様に、2016年度第2四半期のVTBレートは1,281,467,662ドルで、VTBボリュームは1,210,710,978ドルです。 VTBレートとVTBボリュームを追加すると、70,756,678ドルを取得します。これは、ピボットテーブルに表示されるVTB合計値です。
前年比の測定値の作成
次のように、前年比測定を作成できます-
_YoYレート：=（[実際の年間CPH]/12- [前年の実際の年間CPH]/12） *[実際の総ヘッド数] _
前年のボリューム測定の作成
次のように、年間のボリューム測定を作成できます-
YoY Volume：= [YoY Actual Total Headcount]* [Prior Year Actual Annualized CPH]/12
予測レート測定への分散の作成
次のように、予測レートへの分散測定を作成できます-
_VTFレート：=（[予測年次CPH]/12- [実際の年次CPH]/12） *[実際の総ヘッド数] _
体積測定値を予測するための分散の作成
次のように予測ボリュームへの分散メジャーを作成できます-
VTFボリューム：= [VTF総ヘッドカウント]* [予測年次CPH]/12
予測メジャーに対する分散を使用したデータの分析
次のようにパワーピボットテーブルを作成します-
- 日付テーブルからの会計四半期と会計月のフィールドを行に追加します。
- 実績年次CPH、予測年次CPH、VTFレート、VTFボリューム、VTF合計のメジャーを値に追加します。
- 日付テーブルの会計年度フィールドとアカウントテーブルのサブクラスをフィルターに追加します。
- 会計年度フィルターで2016年度を選択します。
- サブクラスフィルターで「人」を選択します。
- 2016年度第1四半期および2016年度第2四半期の値のフィルター行ラベル。
予算レートメジャーに対する予測差異の作成
あなたは次のように予算レートメジャーへの予測分散を作成することができます-
_予測VTBレート：=（[予算年間CPH]/12- [年間年間CPHの予測]/12） *[総従業員数の予測] _
予算量メジャーに対する予測差異の作成
次のように、予算量メジャーへの予測分散を作成できます-
Forecast VTB Volume：= [Forecast VTB Total Head Count]* [Budget Annualized CPH]/12
予算メジャーに対する予測分散を使用したデータの分析
次のようにパワーピボットテーブルを作成します-
- 日付テーブルからの会計四半期と会計月のフィールドを行に追加します。
- 測定値に、予算年次CPH、予測年次CPH、予測VTBレート、予測VTBボリューム、予測VTB合計を値に追加します。
- 日付テーブルの会計年度フィールドとアカウントテーブルのサブクラスをフィルターに追加します。
- 会計年度フィルターで2016年度を選択します。
- サブクラスフィルターで「人」を選択します。
- 2016年度第1四半期および2016年度第2四半期の値のフィルター行ラベル。
