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