(2019/6/22)ExcelのSUMIFS関数で作られるテーブルとPower BIで作ったテーブルの数字が合わないときのTips
本記事で書く内容
ExcelのSUMIFS関数で集計された結果と同じ値のテーブルを作りたいとき、
リレーションシップによって集計値が合わない部分を無理やり合わせる方法です。
例:プロジェクトの予算と実績比較テーブル
前提1: 予算と実績が別々に管理されています
前提2: 2つの表はプロジェクトIDで紐づいています
前提3: プロジェクト実施部署≠予算拠出部署
プロジェクトIDに紐づく予算管理表の部署名と、プロジェクト管理表上の部署名は必ずしも一致しません。また1つのプロジェクトに対し複数の予算が使われる場合もあります。リレーションシップの変更も禁じられています。
前提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は別途計算する
対プロジェクト部門実績 =
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.実績])
)
※より良い方法が見つかり次第、更新したいと思います。アイデア等ありましたらご指摘いただけると幸いです。
Author And Source
この問題について((2019/6/22)ExcelのSUMIFS関数で作られるテーブルとPower BIで作ったテーブルの数字が合わないときのTips), 我々は、より多くの情報をここで見つけました https://qiita.com/yukiueno/items/18e06de9df900da0ba42著者帰属:元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .