Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

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

目標、実績、達成率、昨年比を持つ表です。これをPowerBIで作る際、どのようにモデリングをすれば良いのか、いろいろ試行錯誤をした結果、得られたことを書きます。
利用用途としては、単月、四半期ごとに売上目標を設定し販売活動をしている、セールス部門の計数管理を想定しています。

1.マスターデータと実績蓄積データはテーブルを分け、表示項目は常にマスターデータを利用する

ここでは素材として目標マスタと実績テーブルがあると仮定します。
quiita_実績と目標説明.png

失敗例

PowerBIを触り始めてすぐにぶち当たったのが、エクセルでは簡単にできる予算、実績、達成率という非常にシンプルな表をうまく実現できなかったことです。

上の表では、目標は年月と社員IDを持つレコードで構成されているので、愚直に、2つの列を結合してテーブルリレーションを作成しました。ところが、予算設定が有るけれど、まだ実績が無い場合、集計がうまく行きません。また、通常目標は年月、Qなど一定期間で持つ場合が多いため、期間選択のスライサーを使いたいところですが、両方のデータに年月があり、そもそもどちらの"年月"使っていいかわかりませんでした。

quiita_間違えたリレーション.png

image.png

で、これはうまく行きません。
image.png

成功例

そこで、モデリングをこのように変更します。
quiita_OKリレーション_PBI側.png

マスターデータ(ここではdate_master、社員マスタ)と蓄積系のデータ(ここでは実績データと目標マスタ)はテーブルを分ける、表側項目やスライサー、フィルター項目は常にマスターデータから必ず作る、という2点がミソです。これで、目標、実績どちらか片方のデータが無いことで、集計がうまくできなくなることはありません。

quiita_OKビジュアル.png

date_masterはカレンダーテーブルです。年月毎の目標はデータ型を日付に直して、リレーションを作成するとうまく行きます。

quiita_OKリレーション_PBI側_年月目標.png

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

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

ExcelのPivotテーブルに慣れていると、フィールドをそのままビジュアルに突っ込んでしまいます。
quiita_メジャーを使わない図.png

Pivotテーブルはフィールドを突っ込むといい感じに合計、カウントに切り替えてくれますが、PowerBIではフィールドに対してどういう集計をするのかをメジャーで細かく定義する必要があります。これは最初は面倒ですが、一度メジャーを作っておけば、このメジャー自体を再利用できるので作っておきましょう。

quiita_メジャー使う図.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

ana_aki
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away