PowerBIに興味の有る方はおそらくExcelでこのような表を作った経験があるのではないでしょうか。
目標、実績、達成率、昨年比を持つ表です。これをPowerBIで作る際、どのようにモデリングをすれば良いのか、いろいろ試行錯誤をした結果、得られたことを書きます。
利用用途としては、単月、四半期ごとに売上目標を設定し販売活動をしている、セールス部門の計数管理を想定しています。
1.マスターデータと実績蓄積データはテーブルを分け、表示項目は常にマスターデータを利用する
ここでは素材として目標マスタと実績テーブルがあると仮定します。
失敗例
PowerBIを触り始めてすぐにぶち当たったのが、エクセルでは簡単にできる予算、実績、達成率という非常にシンプルな表をうまく実現できなかったことです。
上の表では、目標は年月と社員IDを持つレコードで構成されているので、愚直に、2つの列を結合してテーブルリレーションを作成しました。ところが、予算設定が有るけれど、まだ実績が無い場合、集計がうまく行きません。また、通常目標は年月、Qなど一定期間で持つ場合が多いため、期間選択のスライサーを使いたいところですが、両方のデータに年月があり、そもそもどちらの"年月"使っていいかわかりませんでした。
で、これはうまく行きません。
成功例
そこで、モデリングをこのように変更します。
マスターデータ(ここではdate_master、社員マスタ)と蓄積系のデータ(ここでは実績データと目標マスタ)はテーブルを分ける、表側項目やスライサー、フィルター項目は常にマスターデータから必ず作る、という2点がミソです。これで、目標、実績どちらか片方のデータが無いことで、集計がうまくできなくなることはありません。
date_masterはカレンダーテーブルです。年月毎の目標はデータ型を日付に直して、リレーションを作成するとうまく行きます。
時系列の推移を可視化するときは、カレンダーテーブルを利用すると後々楽です。月、Q,などの時間軸が簡単に切り替えられるようになりますし、後述のタイムインテリジェンス関数で、過去比較も楽になります。
2.数値の集計にはメジャーを使う
ExcelのPivotテーブルに慣れていると、フィールドをそのままビジュアルに突っ込んでしまいます。
Pivotテーブルはフィールドを突っ込むといい感じに合計、カウントに切り替えてくれますが、PowerBIではフィールドに対してどういう集計をするのかをメジャーで細かく定義する必要があります。これは最初は面倒ですが、一度メジャーを作っておけば、このメジャー自体を再利用できるので作っておきましょう。
このように達成率、昨対比率など、非常に簡潔に書けます。
必ずメジャーを使うようにすれば何度も同じメジャーを書いてしまう非効率を避けることができます。
3.時系列比較したい場合、カレンダーテーブルとタイムインテリジェンス関数を使う
PowerBIを使い始めた当初、昨年同時期の実績を算出するのにこんな感じの複雑なDax式を書いていました。
。
イメージ
X関数やフィルターコンテキストなどDax特有の概念を理解する必要があり、保守が大変そうだと感じていました。さらにその後YTD,年間移動累計など実現したいことが増えて行った場合、Daxが複雑になり、今後の保守がますます不安になります。
フォーラムやYoutubeなどを徘徊し、よくよくDaxのリファレンスを読むと、時系列比較に特化したタイムインテリジェンスいう糖衣関数が用意されていることがわかりました。これが非常にわかりやすい表現で、可読性抜群。これを発見してからはタイムインテリジェンス関数のみ利用するようにしています。
よく使う時系列集計
以下よくつかうものです。
モデリング構成は前述のと同じです。
現在の実績
昨年同時期の実績
前年比
12ヶ月移動累計
dateaddの中身をアレンジすれば3ヶ月移動累計などもできます
会計年度7月始まりの場合の売上実績YTD
ちなみに前述"2.集計にはメジャーを使う"を守っていると、何度もSUMXを書かなくていいので楽です。さらに、前述の1を守っていれば、Dax独特のコンテキスト評価に悩まされることが減ると思います。
まとめ(2018-12-27 追記)
1.マスターデータと実績蓄積データはテーブルを分け、表示項目は常にマスターデータを利用する
2.数値の集計にはメジャーを使う
3.時系列比較したい場合、カレンダーテーブルとタイムインテリジェンス関数を使う
なお、1の概念についてはスタースキーマという名前がついているようです。
PowerBIのレポート作成においてモデリングは非常に重要です。その重要なエッセンスはこちらの動画から学ぶことができます。
https://www.youtube.com/watch?v=RiHpkN0gfPM
