Go to Qiita Advent Calendar 2024 Top
LoginSignup
27
35

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

Microsoft Power BIAdvent Calendar 2017

Day 18
@bi-nonet

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

Last updated at Posted at 2017-12-18

PowerBIに興味の有る方はおそらくExcelでこのような表を作った経験があるのではないでしょうか。
image.png

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

サンプルファイル

pbixファイル

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

素材として目標と実績テーブルがあると仮定します。
image.png

失敗例

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

image.png

image.png

以下は集計表です。これはうまく行きません。
image.png

成功例

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

  • 社員ID,年月をそれぞれマスターテーブル(カレンダー、社員マスター)として切り出す
  • カレンダーテーブルは連続した日付のテーブルとする
  • 目標、実績テーブルそれぞれと１対多でリレーションを設定する
    image.png

目標テーブルの年月はデータ型を日付に変換してカレンダーとリレーションを設定します。
image.png

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

image.png

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

image.png

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

ExcelPivotテーブルに慣れていると、フィールドをそのままビジュアルに突っ込んでしまいます。
image.png

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

image.png

また、一度メジャーを作ればメジャー自体を再利用できます。メジャーを使うことで何度も同じことを書いてしまう非効率を避けることができます。この紫の部分は別途定義したメジャーを再利用しています。
image.png

3.時系列比較したい場合、カレンダーテーブルとタイムインテリジェンス関数を使う

PowerBIを使い始めた当初、昨年同時期の実績を算出するのにこんな感じの複雑なDax式を書いていました。

イメージ
image.png

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

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

よく使う時系列集計

現在の実績

image.png

昨年同時期の実績

image.png

前年比

image.png

12ヶ月移動累計

image.png
dateaddの中身をアレンジすれば3ヶ月移動累計などもできます

会計年度7月始まりの場合の売上実績YTD
image.png

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

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

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

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

2021-05-04 追記

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
27
35

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?