この記事ではPower BI上で異なるテーブルの列データをマージする方法について紹介していきます。マトリックステーブルを作成した際に、各行は正しい値が出力されているが、合計の値がおかしいといった問題に直面したことはありませんか？
この記事を読むことで以下の疑問を解消できます。
・どうやってマージするのか？
・そもそもなぜ列をマージする必要があるのか？
・マージしなくても問題ないのではないか？
それでは以下で説明していきます。
結論
・Power Query側で紐づけたいデータを指定してマージすることで異なるテーブルから列を追加できる。
・一つのテーブルにデータをまとめることでマスタテーブルに必要な情報を後から追加できる。
・単純なDAXのSUMでは複数のテーブルを経由した間接的なリレーション同士を正しく集計できないため、マージか仮のテーブルを作成する必要がある。
・マージ以外の解決法はVAR関数とSUMX関数を組み合わせたDAXを作る
今回は以下の販売実績のテーブルとマスタテーブルを例にして説明していきます。
DAXで行の集計は合うが、合計が合わない問題
・まずPower Queryを開きます。
・販売実績は製品マスタと単価コード列でリレーション、単価コードも製品マスタと単価コード列でリレーションを作成します。
・ここで次のDAXを作成します。
・このDAXと製品マスタを使ってマトリックステーブルを作ると以下のようになります。
このように各行の計算結果は正しいのですが合計の値がSUM販売数の合計と単価の合計の掛け算になってしまいます。
計算ミスの原因は上図のように単価のテーブルと販売数のテーブルに直接的なリレーションが存在しないこと、SUM関数で集計したDAX同士を掛け合わせていることです。
この問題を解決するには、
1．製品マスタに単価列をマージする
2．VAR関数で仮のテーブルを作り、SUMX関数で集計する
の二つの方法が挙げられます。
Power Queryで列のマージを行う方法
上記のレポートでPower Queryを開き、クエリのマージをクリック
製品マスタと単価コードのテーブルを単価コード列を基準にしてOKを選択
製品マスタのテーブルに単価コードテーブルが追加されます。
単価コードテーブル列の右上をクリックして単価コード列のみを製品マスタに追加します。
これで単価列のマージは完了です。
これで販売数も製品マスタに追加されました。
最後に販売数＊単価の列を追加してテーブルに反映させれば完了です。
このように一つのテーブルにまとめることで同じ行の異なる列の集計が可能です。
VAR関数で仮のテーブルを作り、SUMX関数で集計する方法
こちらはDAXを一つ作成することで完了です。
以下のDAXを作ります。
各関数の簡単な説明ですが、
VAR…名前付きの変数として式の結果を格納できる (VAR…RETURNで閉める)
SUMMARIZE (テーブル名, 列１, “名前”, 式…)…基準となるテーブルを指定してそのテーブルの列と作成したDAXメジャー、参照元のテーブル (相手がマスタ)の列を一つのテーブルにまとめることができる
SUMX (テーブル名, 式)…指定したテーブルの列の合計を求める。SUMと異なり、同じ行の異なる列の四則演算が可能
先ほどのクエリのマージを行った式と同様の結果が得られました。初めのうちはクエリのマージの方が単純ですが、行数の大きいデータを扱う場合、列を複数追加するとその分データの読み込みに時間がかかってしまいます。
VARでDAX上に仮のテーブルを作成することで余分な時間の削減も可能です。また、作成されたPower BIレポートを基に別のレポートを作成する場合、後から既存のテーブルに列を追加することができませんのでこちらの方法でしか、解決できない場面も出てくるかと思います。ぜひ使いこなしていきましょう！
まとめ
クエリのマージ方法となぜマージが必要か理解できましたでしょうか。
Power BIはExcelと比較して横方向の集計に癖があります。そのため、集計を行う際は一つのテーブルにまとめる、またはDAX上で一つのテーブルを作成し集計を行いましょう！
Power BIはまだまだ日本語の情報が少ないですが、Shiminlogでは基礎的な情報から応用的な内容まで記事を追加していきます。ぜひ他の記事もご覧ください。
コメントを残す