目標実績管理(予実管理)のためのモデリング試行錯誤


PowerBIに興味の有る方はおそらくExcelでこのような表を作った経験があるのではないでしょうか。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/41698/5c6d667c-d33f-81b0-f54e-6257ca11f620.png)


Excelでは簡単に作れるこの表をPowerBIで作るにはモデリング部分にすこしコツが必要になります。この記事ではPowerBIでこのような表を作る時、主にモデリングの観点からどういった実装が必要になるかをお伝えします。
利用用途としては、単月、四半期ごとに売上目標を設定し販売活動をしているセールス部門の計数管理を想定します。

### サンプルファイル
[pbixファイル](https://github.com/bi-nonet/powerbi-sample/blob/main/%E7%9B%AE%E6%A8%99%E5%AE%9F%E7%B8%BE%E7%AE%A1%E7%90%86%E3%81%AE%E3%83%A2%E3%83%87%E3%83%AA%E3%83%B3%E3%82%B0%E3%82%B5%E3%83%B3%E3%83%97%E3%83%AB.pbix)

## 1.マスター(分析軸)と実績蓄積はテーブルを分け、表示項目はマスターテーブルを利用する

素材として目標と実績テーブルがあると仮定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/41698/96b35175-b709-72d4-fbbd-12aae17d9313.png)


### 失敗例

上の表は年月と社員IDを持つレコードで構成されています。最初はこの共通項目に着目し、```社員ID-年月```で結合項目を作りテーブルリレーションを設定しました。ところが、目標と実績どちらかしかない場合は集計がうまくできません。また目標は年月Qなどで切り替えたいため、期間選択スライサーを使いたいところですが、目標、実績どちらの```年月```使えばよいかわかりませんでした。前年比の実現方法もわかりませんでした。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/41698/509e5470-74f4-53b4-e73c-76c69bbb4824.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/41698/b26056c3-1979-ef40-e561-16dbec7a5973.png)

以下は集計表です。これはうまく行きません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/41698/d883faaf-daa6-ce90-d882-d13544af9c86.png)



### 成功例
そこで、モデリングをこのように変更します。

 * 社員ID,年月をそれぞれマスターテーブル(カレンダー、社員マスター)として切り出す
 * カレンダーテーブルは連続した日付のテーブルとする
 * 目標、実績テーブルそれぞれと１対多でリレーションを設定する
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/41698/f02825ae-641e-1e6b-78e4-72491a3182d5.png)


目標テーブルの年月はデータ型を日付に変換してカレンダーとリレーションを設定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/41698/e4304841-562a-6fd2-8c5d-bf44582a3a93.png)


さらに表側項目やスライサー項目は常にマスターテーブルから作ります。これで、目標、実績どちらか片方しかデータが無い場合でも正しく集計を表示できます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/41698/318dbc65-4f11-703f-4fc8-520f750628b8.png)


時系列の推移を可視化するときは連続した日付を含む、**カレンダーテーブル**を利用します。月、Qなどの時間軸はラベルとして切り替えられるようになり、後述のタイムインテリジェンス関数で過去比較も楽になります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/41698/9457a4e3-07bb-d993-6bce-944ae5b33b35.png)


## 2.数値の集計にはメジャーを使う

ExcelPivotテーブルに慣れていると、フィールドをそのままビジュアルに突っ込んでしまいます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/41698/072fa95f-f8b2-55e7-d155-d2261a218de8.png)


ExcelPivotテーブルはフィールドを突っ込むといい感じに合計、カウントに切り替えてくれます。PowerBIでも同じような動きをしますが、フィールドに対する集計を個別に定義する(＝メジャーを作る)ほうがわかりやすくメンテしやすいモデリングになりました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/41698/e9179cc9-afab-54d7-11e5-96f00b7bd355.png)

また、一度メジャーを作ればメジャー自体を再利用できます。メジャーを使うことで何度も同じことを書いてしまう非効率を避けることができます。この紫の部分は別途定義したメジャーを再利用しています。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/41698/e64abf6e-c897-cc08-90cf-e7bbfba1b7cf.png)


## 3.時系列比較したい場合、カレンダーテーブルとタイムインテリジェンス関数を使う
PowerBIを使い始めた当初、昨年同時期の実績を算出するのにこんな感じの複雑なDax式を書いていました。

**イメージ**
![image.png](https://qiita-image-store.s3.amazonaws.com/0/41698/629cbbe9-4219-f42e-7657-e3fa4bb882e9.png)

X関数やフィルターコンテキストなどDax特有の概念理解が必要で、保守が大変そうだと感じていました。またYTD,年間移動累計など実現したいことが増えた場合、Daxが複雑になり保守がますます不安になります。


フォーラムやYoutubeなどを徘徊し、よくよくDaxのリファレンスを読むと、時系列比較に特化した**[タイムインテリジェンス](https://msdn.microsoft.com/ja-jp/library/ee634763.aspx)**いう糖衣関数が用意されていることがわかりました。これが非常にわかりやすい表現で、可読性抜群。これを発見してからはタイムインテリジェンス関数のみ利用するようにしています。

### よく使う時系列集計

#### 現在の実績
![image.png](https://qiita-image-store.s3.amazonaws.com/0/41698/0b228c43-cbab-c03e-3d25-9e80694109bb.png)

#### 昨年同時期の実績
![image.png](https://qiita-image-store.s3.amazonaws.com/0/41698/24789d8a-3517-22a5-542a-8ea9ef7bdfce.png)

#### 前年比
![image.png](https://qiita-image-store.s3.amazonaws.com/0/41698/f81406ff-98fd-5a9a-5f0f-60e506a71e3c.png)

#### 12ヶ月移動累計
![image.png](https://qiita-image-store.s3.amazonaws.com/0/41698/dbb9f9b9-5d1f-0eac-4d37-a91ba5eee13e.png)
dateaddの中身をアレンジすれば3ヶ月移動累計などもできます

**会計年度7月始まりの場合の売上実績YTD**
![image.png](https://qiita-image-store.s3.amazonaws.com/0/41698/3fdab392-ba6d-16c5-67d7-c20f8e0f94a1.png)

前述"2.集計にはメジャーを使う"を守っていると、何度もSUMXを書くことを避けられます。さらに、前述1を守っていれば、Dax独特のコンテキスト評価に悩まされることが減ると思います。

## まとめ(2018-12-27 追記)

1. マスターデータと実績蓄積データはテーブルを分け、表示項目は常にマスターデータを利用する
2. 数値の集計にはメジャーを使う
3. 時系列比較したい場合、カレンダーテーブルとタイムインテリジェンス関数を使う

なお、1の概念については**スタースキーマ**という名前がついているようです。
PowerBIのレポート作成においてモデリングは非常に重要です。その重要なエッセンスはこちらの動画から学ぶことができます。
https://www.youtube.com/watch?v=RiHpkN0gfPM

## 2021-05-04 追記

 * PowerBIDesktopのアップデートにより画面イメージが古くなってしまったため、新しいものに差し替えました。
 * サンプルファイルを用意しました。
 * 全体的に読みやすく編集しました