情シス仕事の備忘録

自身の備忘録を兼ねて、情シス仕事で役に立ちそうな情報を掲載しています

トップ > PowerBI > Power BI 複数テーブルを使用したレポート作成

Power BI 複数テーブルを使用したレポート作成

PowerBI データ分析

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]を選択します。
※この例では、人数テーブルに売上テーブルの項目である売上高を取り込みます

 

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

 

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

 

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

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

※先ほど取り込んだ列の名前は、元のテーブルの名前が含まれており長かったため、”売上高(百万円)”に変更しています

 

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

 

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

 

グラフから詳細データへのドリルスルーを設定する

グラフの特定の凡例(この例では特定の業種)に対し、詳細データを確認したい場合、参照先の詳細データにドリルスルーを設定します。
詳細データを用意し(この例では[元データ]シート)、[視覚化]ペインの下の方にあるドリルスルーの設定項目に、ドリルスルーしたい項目をドラッグ&ドロップします。
※ドリルスルー設定に関する公式記事：
　Power BI レポートでドリルスルーを設定する - Power BI | Microsoft Learn

 

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

 

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

 

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

工程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と同じような操作が必要となりますので、扱いに慣れておくと対応の幅が広がって良いと思います。

※Power BIにおけるDAXの基本に関する公式記事：
　Power BI Desktop における DAX の基本を学習する - Power BI | Microsoft Learn