【Power BI】複数テーブル使用レポート作成

Microsoft社が提供するBI(Business Intelligence)ツール「Power BI」について、複数テーブルを使用した簡単なレポート作成例を紹介します。

操作環境：Windows 11
データベース：SQL Server Express 2022(オンプレミス)
BI構成：Power BI Desktop、Power BI Service(Fabric無料版)

使用データ・作成レポートについて
複数項目リレーションシップ設定
テーブル間項目による計算列追加
ドリルスルー設定
メジャーによる集計項目追加
おわりに

使用データ・作成レポートについて

この例では、以前の記事(以下リンク)で紹介した、政府統計データ(e-Stat)のサービス業の人数および売上高の推移(月間値を年平均したもの)を使用します。

人数と売上高はSQL Server上の別テーブルに格納しているため、レポート上でこれらを結合し、一人当たり売上高(売上高/人数)を算出して表示します。

複数項目リレーションシップ設定

年と業種コードの二項目でリレーションシップを設定したいのですが、複数項目を設定することはできません。各テーブルに二項目をマージした列を追加し、それに対してリレーションシップを設定する必要があります。

クエリエディタのアイコンを選択します。

[列の追加]を選択し、[ctrl]キーを押しながらマージしたい項目(この例では年と業種コード)を選択し、[列のマージ]を選択します。

*この画面上に表示しきれていませんが、年も選択した状態です(分かり辛くてごめんなさい)

項目の区切り文字や列名を適宜入力し、[OK]を選択します。

マージ列が追加されました。
同じようにもう一つのテーブルも設定します(左のテーブル選択から選択)。

二つのテーブルのマージ列の追加が完了したら、左上の[ホーム>閉じて適用]を選択します。

モデルビューに戻り、一方のテーブルのマージ列をもう一方のテーブルのマージ列へドラッグ&ドロップします。
[新しいリレーションシップ]画面が表示されたら、それぞれのマージ列が選択されていることを確認し、[保存]を選択します。

テーブル間項目による計算列追加

複数テーブルの項目を使った計算列を作成するには、クエリのマージ機能を使って一方のテーブルに他方のテーブルの必要項目を取り込み、その後計算列を追加します。

クエリエディタのアイコンを選択します。

[クエリのマージ>クエリのマージ]を選択します。
工程2で追加したマージ列(年_業種コード)を選択し、結合の種類を適宜選択(*)し、[OK]を選択します。

*この例では、2テーブル間で年_業種コードが一致するレコードのみを扱うため、[内部]としています。一方のテーブルにしかないレコードも含めたい場合等は状況に応じて変更します

他方のテーブルから取り込みたい項目にチェックをつけ、[OK]を選択します。

*この例では、人数テーブルに売上テーブルの項目である売上高を取り込みます

他方のテーブルの項目取り込みが完了したら、左上の[閉じて適用]を選択します。

[レポートビュー]を選択し、[データ]ペインの人数テーブルで右クリックし、[新しい列]を選択します。

以下の通り計算式を設定し、チェックアイコンを選択します。

一人当たり売上高(百万円) = [売上高(百万円)] / [人数(人)]

*図表3-2から4で取り込んだ列の名前は、元のテーブルの名前が含まれており長かったため、”売上高(百万円)”に変更しています

一人当たり売上高(月間)をグラフに反映してみました。
各サービス業の左が人数、右が一人当たり売上高の推移です。
e-Statの統計手法に説明が見つからず推測ですが、人数に稼働時間が考慮されていないかもしれません。パート・アルバイトが多いと一人当たり売上が低くなりますので、この結果が単純に稼ぐ力を示すとは言いにくいです。

こちらは図表3-7のグラフにあった[G 情報通信業]の詳細分類ごとの推移です。
一人当たり売上高については、通信業の高さと下落傾向、および、情報サービス業の低さが気になりました。

ドリルスルー設定

グラフの特定の凡例(この例では特定の業種)に対し、詳細データを確認したい場合、参照先の詳細データにドリルスルーを設定します。

詳細データを用意し(この例では[元データ]シート)、[視覚化]ペインの下の方にあるドリルスルーの設定項目に、ドリルスルーしたい項目をドラッグ&ドロップします。

グラフのシートに戻り、特定の業種を選択して右クリックすると、[ドリルスルー>(参照先シート名)]というメニューが表示されますので、これを選択します。

グラフ上で選択した業種に絞り込まれた状態で詳細データが表示されます。

メジャーによる集計項目追加

工程3で紹介した列の追加に似た機能として、メジャーの追加があります。
列はレコード毎に値を持ちますが、メジャーは集計項目のためそうではありません。
メジャーの使用例として、サービス業全体の売上高と一人売上高の推移を算出し、グラフ化してみます。

[データ]ペインの人数テーブルで右クリックし、[新しいメジャー]を選択します。

まずはサービス業全体の売上高です。
以下の通り計算式を設定し、チェックアイコンを選択します。

サ業全体売上高(百万円) = 
  sumx(
    KEEPFILTERS(VALUES(‘業種別人数推移’[年])),
    CALCULATE(SUM(‘業種別人数推移’[売上高(百万円)]),
      right(‘業種別人数推移’[業種コード], 3) == “000”,
      convert(‘業種別人数推移’[業種コード], integer) >= 2000,
      convert(‘業種別人数推移’[業種コード], integer) <= 20000
    )
  )

*売上高(業種コードが大分類のレコードで絞り込み)を年単位に合計しています

次はサービス業全体の一人当たり売上高です。
以下の通り計算式を設定し、チェックアイコンを選択します。

サ業全体一人当たり売上高(百万円) =
  sumx(
    KEEPFILTERS(VALUES(‘業種別人数推移’[年])),
    CALCULATE(SUM(‘業種別人数推移’[売上高(百万円)]),
      right(‘業種別人数推移’[業種コード], 3) == “000”,
      convert(‘業種別人数推移’[業種コード], integer) >= 2000,
      convert(‘業種別人数推移’[業種コード], integer) <= 20000
    )
  ) / sumx(
    KEEPFILTERS(VALUES('業種別人数推移'[年])),
    CALCULATE(SUM('業種別人数推移'[人数(人)]),
      right('業種別人数推移'[業種コード], 3) == "000",
      convert('業種別人数推移'[業種コード], integer) >= 2000,
      convert('業種別人数推移'[業種コード], integer) <= 20000
    )
  )

*売上高(業種コードが大分類のレコードで絞り込み)を年単位に合計し、同様に人数(業種コードが大分類のレコードで絞り込み)を年単位に合計したもので割っています

メジャーによる集計項目を使ったグラフです(棒グラフが売上高、折れ線グラフが一人当たり売上高)。

おわりに

データベースでのSQL作成に慣れていると、Power BIでのクエリの加工や列追加・メジャー追加を行うのは少々煩わしく感じます(DAXの扱い・・・)。他のレポートでも共通的に使用するクエリであれば、データベースにビューなどを用意して、Power BI側は参照するだけにした方が保守性が高そうです。

とはいえ、ExcelのPower QueryでもPower BIと同じような操作が必要となりますので、扱いに慣れておくと対応の幅が広がって良いです。

当ブログ内の関連記事

elmgrn.hatenablog.com

IT Hands-on Lab

小規模組織向けIT環境の構築・運用に役立つ情報を、ハンズオン形式で紹介しています。