(2019/6/22)ExcelのSUMIFS関数で作られるテーブルとPower BIで作ったテーブルの数字が合わないときのTips

本記事で書く内容

ExcelのSUMIFS関数で集計された結果と同じ値のテーブルを作りたいとき、
リレーションシップによって集計値が合わない部分を無理やり合わせる方法です。

例：プロジェクトの予算と実績比較テーブル

前提1: 予算と実績が別々に管理されています

前提2: 2つの表はプロジェクトIDで紐づいています

前提3: プロジェクト実施部署≠予算拠出部署

プロジェクトIDに紐づく予算管理表の部署名と、プロジェクト管理表上の部署名は必ずしも一致しません。また１つのプロジェクトに対し複数の予算が使われる場合もあります。リレーションシップの変更も禁じられています。

前提4: 結果はSUMIFS関数の部署名ごとの集計と一致させます

前提5: 前提4のExcelが間違った集計結果だと言ってはいけない

Power BIの正しい使い方だとは思いませんが、強いられている。

何が問題でどうやって解決するのか？

管理表の部署名を元に集計した場合は、Power BI上でプロジェクトIDによるリレーションが張られているため計算が合いません。

問題は部署名ごとに集計ができないこと

DAXを利用してProject IDによるリレーションを無効化した上で、部署名のマッチングのみで集計します。

テーブル間のリレーションは変更しないまま、目的の値を取ってくることができました。

Power BI内のテーブル名はそれぞれ下記とします。

• プロジェクト管理表：Projects
• 予算管理表：Costs
• Project IDでFull Outerオプションでマージしたテーブル：Mapper

DAXによる集計方法

1. プロジェクト管理表と予算管理表で部署名が一致するものだけ集計する
2. 部署名が合わないものは、ALLEXCEPT関数でプロジェクト管理表側の部署名のみに合わせて集計する
3. Totalは別途計算する

Project_Cost.DAX

対プロジェクト部門実績 = 
    IF(
        MIN(Projects[部署]) = MAX(Projects[部署]),
        CALCULATE(
            SUM('Mapper'[Costs.実績]), 
            FILTER('Mapper', [部署]=[Costs.部署])
        )
        + CALCULATE(
            SUM('Mapper'[Costs.実績]), 
            FILTER(
                ALLEXCEPT('Mapper', Costs[部署]),
                AND([部署]<>[Costs.部署], MIN(Projects[部署]) = [Costs.部署])
            )
        ),
        SUM('Mapper'[Costs.実績])
    )

※より良い方法が見つかり次第、更新したいと思います。アイデア等ありましたらご指摘いただけると幸いです。