柔軟性重視！月別予算+実績明細モデリング

この記事は「現場で使えるPower BI 4つのモデリングパターン - 予実管理編」の連載記事です。今回は「月次予算+実績明細」パターンを紹介します。

前回までは予算と実績の粒度を揃える方法を紹介しましたが、今回はデータの粒度を揃えずに、実績明細をそのまま活用する方法です。詳細な実績分析と予算管理を両立できるのが特徴です。

こんな方におすすめ

• 実績の詳細分析と予算管理を両立したい方
• 商品別・顧客別など詳細な切り口で分析したい方
• 明細データを活かした柔軟なレポートを作りたい方

この記事で学べること

• 実績明細と月次予算を組み合わせるモデリング手法
• 粒度の異なるデータ間のリレーション設定（ブリッジテーブル）
• N対Nリレーションを回避する方法

今回加工したデータのイメージは下記のとおりです。

実績ファクト

予算ファクト

Keyブリッジテーブル

このイメージでデータを作ることができれば、グラフにするのはとても簡単です。問題は、この状態のデータを作り出すことです。

データ加工手順(Power Query)

データの加工はPower BIのデータ変換ツールであるPower Queryで行います。全ての工程をステップバイステップで説明しようとすると大変長くなってしまい、1記事では収まらないボリュームになってしまうため、ポイントに絞った説明になる点はご了承ください。

なぜブリッジテーブルが必要か？

前回まではは予算と実績のデータの粒度を揃えましたが、今回はデータの粒度は揃えていません。予算データはありのままの粒度、実績データもありのままの粒度で取り込みました。

これを素直にやろうとすると、リレーションがN対Nになってしまいます。N対Nのままだとフィルタが思ったように機能しなかったり、集計が思った通りに集計されなかったりするという問題が起きます。

N対Nを回避するため、ブリッジテーブル（中間テーブルとも呼ばれます）を作ります。

ブリッジテーブルは予算側で持っている全てのディメンション（切り口）を持たせ、ユニークキーを付与しています。このキーを実績データと予算データにも付与して、リレーション設定をします。

ステップ1：ブリッジテーブルを作成する

ブリッジテーブルに必要なカラムは、2つのファクトテーブルである予算データと売上データに共通する項目です。予算は月別・カテゴリ別・地域別に持っていたので、これらがブリッジテーブルに持つカラムとなります。

網羅リストの作成：

年月、地域ID、カテゴリIDの組み合わせを網羅的にしたリストを用意します。作り方は以前の記事でも紹介した方法で、各マスタにダミー列（全て1）を追加して、完全外部結合です。

ユニークキーの付与：

このリストが1行ずつユニークになるようなキーを追加します。今回のキーは、列の追加→カスタム列→「年月-地域ID-カテゴリID」で作りました。

これでブリッジテーブルは完成です。

注意点：
Power BIのリレーション設定は複数カラムで紐づけることができません。このため、複数カラムの情報をつないだ1つのキーを用意しています。

ステップ2：予算データにブリッジテーブルのキーを付与する

予算データに、列の追加→カスタム列→「年月-地域ID-カテゴリID」でキーを付与しました。これを予算ファクトとします。

ステップ3：実績データにブリッジテーブルのキーを付与する

実績明細データに、列の追加→カスタム列→「年月-地域ID-カテゴリID」でキーを付与しました。

これを実績ファクトとします。

モデリング設定(Power BI Desktop)

ここからはPower BI Desktopです。

カレンダーテーブルの作成

日別のカレンダーテーブルをDAX関数で自動生成します。また、今回は予算は月別なので月のカレンダーテーブルを別に用意しました。
実績と予算の両方を月でフィルタさせる場合は、月別のカレンダーマスタを使い、実績を明細レベルで見たい時は、日別のカレンダーマスタを使います。

カレンダーテーブルが必要な理由：

1. 年度集計が簡単：4月始まりの会計年度にも対応
2. 時間インテリジェンス関数が使える：前年同月、累計などの計算が容易
3. 期間フィルタが柔軟：四半期、半期などの集計も可能
4. 粒度の異なるテーブル間の橋渡し：日次実績と月次予算を統合的に扱える

リレーション設定

ブリッジテーブルのキーを使って、予算ファクトとブリッジテーブル、実績ファクトとブリッジテーブルをリレーションします。

ポイント：
実績と予算は直接結合せず、ブリッジテーブルを介して関連付けます。これにより、N対Nリレーションを回避しながら柔軟な分析が可能になります。

メジャーの作成(前年同月実績や予算差等)

テーブルビューでデータを確認し、関数を作ります。テーブルビューを開いたら、ついでに金額や％のフォーマットを整えて設定しておくと、グラフや一覧表を作成した時にきれいに見せることができます。

作った関数は下記の通りです。全て新しいメジャーで作成しています。

// 実績合計
売上実績 = SUM('実績ファクト'[売上])

// 予算合計
売上予算 = SUM('予算ファクト'[売上予算])

// 予算との差額を計算
予算差 = [売上実績] - [売上予算]

// 予算達成率を計算（0除算を回避）
予算比 = 
DIVIDE(
    [売上実績],
    [売上予算],
    BLANK()
)

// 前年同月の実績を取得
前年同月実績 = 
CALCULATE(
    [売上実績],
    DATEADD('カレンダーマスタ'[日付], -12, MONTH)
)

// 年度累計を計算（4月始まり）
年度累計実績 = 
CALCULATE(
    [売上実績],
    DATESYTD('カレンダーマスタ'[日付], "03/31") //4月始まり
)

重要な注意点：

予算ファクトと実績ファクトを一緒に見せるビジュアルでは、「日のカレンダーテーブル」の年月ではなく、予算ファクト側の年月を使ってください。

日のカレンダーテーブルは実績ファクトに紐づけられていて、予算データとは方向性が逆です。予算ファクトを月別に見せたい時に日のカレンダーテーブルの年月は使えません。予算ファクトを月別に見せたい時は月のカレンダーマスタを使ってください。

可視化する

カード、棒グラフ、テーブルを使って可視化します。データも式もできているので、各項目をドラッグ後に見た目の調整をしただけです。

まとめ

このモデリングパターンの特徴

メリット

• 実績の詳細分析が可能（商品別、顧客別など）
• 予算管理も同時に実現できる
• データの粒度を無理に揃える必要がない
• 明細データを活かした柔軟なレポート作成が可能

デメリット・注意点

• ブリッジテーブルの作成が必要
• リレーションの設計がやや複雑
• ブリッジテーブルの行数が爆発的に増えがち
• メジャーの記述が必要（列の直接計算ができない）
• データ量が多い場合、パフォーマンスに注意が必要
• カレンダーテーブルの使い方に制約がある

実装のポイント

今回は粒度の異なるデータ同士をそのまま取り込んで比較させるモデリングをやってみました。最も難しかったポイントは、N対Nを回避させる部分です。

ブリッジテーブルの作成、そのキーを予算ファクトや実績ファクトにも付与しなければならないという部分に至るまでが、試行錯誤でした。

作ってみて、「事前にデータの粒度を揃えておいた方がシンプルで楽だったな」と思いました。

運用面での推奨事項

ブリッジテーブルが必要になると、複数のマスタを組み合わせる必要があります。モデリング作成後に「やっぱりこのマスタも追加したい」となると、作り直しの手間が大きな負荷になってしまいます。

また、ブリッジテーブルは関連するすべてのマスタの全ての組み合わせで作成されることになるので、行数が爆発的に増えがちです。

細かなデータの粒度を失わないという柔軟性は確かにメリットではあるのですが、別にして作った方がシンプルではないかと思いました。

データの粒度を失うことを嫌う方は非常に多いのですが、それは、モデルを分けて用意したら解決することなので、無理にデータの粒度が異なるものを一つにまとめなくてもよいのではないかと思いました。

次回予告

次回は「統合ファクト」パターンを紹介します。粒度の異なる予算と実績を、粒度が異なるまま1つのテーブルに統合する方法を解説します。

1. 現場で使えるPower BI 4つのモデリングパターン - 予実管理編
2. Power BI初心者が絶対知っておくべき重要用語を解説
3. 月次予算+月次実績
4. 日割り予算+月次実績
5. 月次予算+実績明細（本記事）
6. 統合ファクト（次回）
7. 振り返り