PBI合計値の不一致 - テクテク日記

Power BIを使用しているとある時点で遭遇するショッキングな事態が合計値とその上の各セルの合計が一致しない問題です。直近ではお客さんからも問い合わせが入ったこともあり、Power BIでなぜそのようなことが起こるのかについて見ていきたいと思います。なお、結果だけ見たい方は記事の一番下からファイルをダウンロードしてください。

概要＆データモデル
メジャー
まとめ

概要＆データモデル

Excelのピボットテーブルを使っている人であれば、ピボットの集計（小計や総計）はその上にある各項目の合計値と一致するということをご存じかと思います。ところが、Power BIでDAX式を記述していくと、この集計と各項目が一致しないケースに遭遇することがあります。

なぜこのようなことが起こるのでしょうか？結論から言いますと、以下2点が理由となります。

Power BI（or DAX）は、セル（データポイント）ベースで考える必要がある
メジャー同士の計算は要注意

これらを理解するためには具体例を解説しながら理解したほうが良いので、以下今回使用するデータモデルの紹介となります。

ソース：SQLサーバー（Contoso Retail DW）
スキーマ：スタースキーマ
- Sales: 売上データ
- BudgetSales: 予算売上データ
- dStore: 店舗マスタ
- dProduct: 商品マスタ
- Date: 日付マスタ

テーブル概要：
下記ハイライト部分はBravo for Power BIで自動生成されたもの、それ以外はSQLサーバーから抽出

BudgetSalesとSalesテーブルはファクトテーブルですが、デモ用にデータを499行のみ抽出しています。499行にした理由はPower BIの「データの入力」の限界が3,000セルになっているためであり、499行 × 6列（BudgetSalesもSalesもどちらも6列）であれば2,994セルでぎりぎりマニュアルで入力が可能となります。

メジャー

ベースメジャー
SUMやDIVIDE等の関数で算出される基礎的なメジャー
- 計画数量
- 計画売上
- 計画単価
- 実績数量
- 実績売上
- 実績単価

------------------
-- Measure: [計画数量]
------------------
MEASURE BudgetSales[計画数量] = SUM( BudgetSales[SalesQuantityQuota] )
    FormatString = "#,0"

------------------
-- Measure: [計画売上]
------------------
MEASURE BudgetSales[計画売上] = SUM( BudgetSales[SalesAmountQuota] )
    FormatString = "#,0"

------------------
-- Measure: [計画単価]
------------------
MEASURE BudgetSales[計画単価] = DIVIDE( [計画売上], [計画数量] )
    FormatString = "0"

------------------
-- Measure: [実績数量]
------------------
MEASURE Sales[実績数量] = SUM( Sales[SalesQuantity] )
    FormatString = "#,0"

------------------
-- Measure: [実績売上]
------------------
MEASURE Sales[実績売上] = SUM( Sales[SalesAmount] )
    FormatString = "#,0"

------------------
-- Measure: [実績単価]
------------------
MEASURE Sales[実績単価] = DIVIDE( [実績売上], [実績数量] )
    FormatString = "#,0"

まずは通常のメジャーを使用して、以下の各シナリオについて見ていきます。

合計の不一致_シナリオ1

最もシンプルな例がIF関数を使った場合の合計です。下図の通り、売上が50K以上であれば[売上実績]というメジャーを採用し、そうでなければBLANKを返すメジャーを作った場合、合計値は変わりません。

理由は上述の通り、メジャーはセル（データポイント）ベースで評価されるため、合計は444,362であるが故に、IFの条件を満たすことからそのままの数字が返ってきたことになります。Excelのピボットテーブルではこのようにフィルターをコントロールすることができなかったので、DAXで作るメジャーは非常に便利である一方、Excelと同じ考え方でDAX式を記述してしまうと、思わぬ落とし穴にハマってしまいます。

ちなみに、このテーブルで結果が出ている理由は、ビジュアル（テーブルやチャート）では常にフィルターコンテキストが効いているためであり、+ボタンを広げると以下のような結果が返ってきます。

こちらは品目コードレベルに見た結果ですが、全てが空白になっているのが分かります。これでは当然、期待する結果とは大きく異なる可能性があり、このようなロジックをDAXで記述する場合、常に

DAX式は何をスキャンをし、評価しようとしているか

を念頭に置いておくと良いと思います。

例えば、上記DAXの記述を少し変えて、品目コード（ProductKey）に対して、売上が500以上の売上合計を計算したい場合、式と結果は以下のようになります。

これにより、[実績売上]と比較すると[品目別実績売上500以上]というメジャーの数字が小さくなっており、合計も小さくなっていることが分かります。少しドリルダウンしてみると、下図の通り、品目コードレベルで500未満の数字は消えています。

ここで先ほどのIF関数を使ったメジャーと何が違うかを比較してみますと、IFバージョンは列を何も指定していないのに対して、新しいメジャーでは品目コード（ProductKey）をスキャンして、このうち500未満の品目をリストから除外し、残った品目コードだけで[実績売上]を返すというビジネスロジックになっています。その結果、一番下の合計でも品目コード「417」や「425」といったリストが除外されており、売上が500以上のリストによって合計が算出されるようになっています。

なお、このメジャーではFILTERというを関数を使用しましたが、DAXにはコンテキストの概念が2つあり、Row ContextとFilter Context、そしてそのコンビネーションであるContext Transitionの3つについてしっかり理解しておく必要があります。

CALCULATE関数を頻繁に使うようなった場合、これら評価コンテキスト（Evaluation Context）に対する理解が非常に重要となってきますので、十分に時間を使って理解されることをお勧めします。

合計の不一致_シナリオ2

シナリオ2ですが、こちらはメジャー同士の計算によって合計が一致しなくなるケースです。単価差額というメジャーを作りますが、計算式は以下の通り。

単価差額 = （計画単価 - 実績単価) × 実績数量

計画単価 = 計画売上 ÷ 計画数量

実績単価 = 実績売上 ÷ 実績数量

既にベースメジャーで殆どの計算式を定義済ですので、ここでは単価差額の計算式だけがポイントとなります。また、単価差額というメジャーの意味は、数量を一定として仮に計画単価で販売が行われた場合、売上に与えるインパクトが実績単価の場合と比べてどれほどあったか、というものです。言い換えれば、単価差額がプラスの場合、実績単価が計画単価よりも小さい＝（本来の単価で販売できなかったことにより）売上損失が生じている、ことを意味します。

一見すると、単価差額は小売業でいうところの値引額（上代売上－実績売上）のように見えますが、上述の通り、”数量を一定として=実績数量で計算”という部分がポイントですので、ロジックと算出結果が違ってきます。

ということで、この単価差額というメジャー、まずは以下のように記述してみます。

-------------------------
-- Measure: [単価差額_Simple]
-------------------------
MEASURE Sales[単価差額_Simple] = ( [計画単価] - [実績単価] ) * [実績数量]
    FormatString = "#,0"

-----------------
-- Measure: [単価差]
-----------------
MEASURE Sales[単価差] =
    IF (
        NOT ISBLANK ( [計画単価] )
            && NOT ISBLANK ( [実績単価] ),
        [計画単価] - [実績単価]
    )
    FormatString = "#,0"

こちらのメジャーですが、上記計算式をそのまま使用しています。[実績数量]、[実績単価]、[計画単価]、[単価差]、[単価差額_Simple]等のメジャーをProductKeyでマップした結果が下図になります。

[実績数量]が空白、[計画単価]に値が入っている部分は、そのProductKeyに[実績売上]が無かったことを意味しますので、[単価差]というメジャーを計算ためにIF + NOT ISBLANKという条件式を設定しています。これにより、[単価差]は実績と計画の両方で値が存在する時のみ、計算される仕組みとなります。

ここで[単価差額_Simple]を見ると計画単価が空白になっている時でも差額が計算されるようになっていますので、これを[単価差]と同じロジックで以下のように書き換えます。

---------------------
-- Measure: [単価差額_調整]
---------------------
MEASURE Sales[単価差額_調整] = 
    IF( NOT ISBLANK( [計画単価] ) && NOT ISBLANK( [実績単価] ), 
         ( [計画単価] - [実績単価] ) * [実績数量]
     )
    FormatString = "#,0"

これで実績・計画のどちらかが空白になっていた場合、[単価差額_調整]も空白になるよう計算されるようになりました。

ここで勘が良い方はお気づきだと思いますが、どちらのメジャーでも合計の数字が同じになっているのが分かります。そうです、シナリオ1の繰り返しになりますが、現在見えている状態でDAXが計算を行っているため、セル単位で評価が行われた結果、

（307 - 241）× 1,842 = 121,995（小数点含めて計算）

となっているのです。

Power BIは非常に便利ですが、唯一不便なところが計算結果のチェック機能がないことで、結果チェックを行うためにはAnalyze in Excel等の機能を使う必要があります。Analyze in Excelの詳細はリンク先を参考して頂くとして、Power BIからExcelを立ち上げたら、ピボットで同じテーブルを作ります。

※ここから先は数字の整合性チェックを行うため、全てExcelベースで見ていきます。なお、ExcelでもPower BI Desktopでも接続しているAnalysis Servicesインスタンスは同じで、計算結果も同じとなります

Excelであれば、セルを集計して合計値を簡単にチェックできますので、ここでは[単価差額_Simple]と[単価差額_調整]の両方を計算します。結果、上図の通り、どちらもDAXで計算された結果（121,995）と違っているのが分かります。ここまで何も難しいことをやっていないのに、いきなり難しい関門にぶち当たりました。なお、[単価差額_調整]を全て合計した結果は11,229であり、この数字が正しい結果となります。

DAXをそこそこ知っている人でも、これにはさすがにショックを受けますが、DAXはセル単位で評価、そのセルの中身（フィルター条件）は何か？ということを思い出せれば問題はほぼ解決されたと言ってもよいでしょう。

以下、DAXのロジックを書き換えます。

-------------------
-- Measure: [単価差額1]
-------------------
MEASURE Sales[単価差額1] = 
    SUMX(
        VALUES( dProduct[ProductKey] ),
        var _actual_price = [実績単価]
        var _plan_price = [計画単価]
        var _qty = [実績数量]
        var _result =
        IF( NOT ISBLANK( _plan_price ) && NOT ISBLANK( _actual_price ), 
            ( _plan_price - _actual_price ) * _qty
        )
        return _result
     )
    FormatString = "#,0"

若干一気に難しくなったかもしれませんが、

合計が合わない場合はSUMXを使おう！

というのが合言葉です。SUMXはテーブルをスキャンするのですが、Row Contextを発生させる関数となりますので、複数の列同士を計算させたりすることができます。VALUES( dProduct[ProductKey] ) によってSUMXがProductKeyというリストをスキャンし、[実績単価]、[計画単価]、[実績数量]の3つのメジャーがそれぞれFilter ContextとしてContext Transitionされて結果を算出し、最後に合計においては条件に合った（NOT ISBLANKの部分）ProductKeyだけが残り、それらの結果が集計される、というのが上記DAX式の読み解き方になります。なお、var（変数）をSUMXの中で使う理由はパフォーマンスと可読性の向上に繋がるためとなります。

この説明文が分からない人は評価コンテキスト（Evaluation Context）とvarについてまずは押さえておくと良いでしょう。こちらの計算結果をExcelピボットにプロットした場合、合計は正しい数値として算出されるようになりました（下図）。

ご覧の通り、前述した11,229という数字が今度は[単価差額1]できちんと算出されるようになりました。ProductKeyをベースとして見た場合、このDAX式が良さそうです。

ここで少しひねりを入れます。今はProductKeyベースで見ていますが、例えばこれを中分類（SubCat）ベースで見た場合、どうなるのでしょうか？

単価差額の合計（=Computers集計）を数値チェックすると、やはり11,229になっていることから、集計とチェック合計は合っていそうです。ただ、困ったことに、[単価差額_Simple]と[単価差額_調整]の2つのメジャーのSubCatベースの数字が[単価差額1]と大きく異なるようになってしまいました。

例えば、Desktopsの数字は[単価差額1]では1,273であるのに対し、他の2つは-17,283となっています。これはどちらの数字が正しいのでしょうか？という問題を再び考える必要が出てきますが、もう一度

DAX式は何をスキャンをし、評価しようとしているか

というのを思い出してみると、[単価差額1]はあくまでdProduct[ProductKey]をスキャンしており、SubCatのDesktopsという中分類をスキャンしているわけではない、ということになります。言い換えれば、[単価差額1]はDesktopsの中にあるProductKeyをスキャンしているのであって、その中で条件に合う（=実績単価と計画単価が両方存在する）品目をフィルター条件として抽出し、計算を行っていることになります。

ここをおさえておくと、[単価差額_Simple]や[単価差額_調整]のDesktopsの算出結果が

（336 - 412）× 225 = -17,283（小数点含めて計算）

という数式によって算出されているのが分かるようになります。ここで答えるべき質問が2つあります。

どちらの数字が正しいのか？
仮に[単価差額_調整]の数字（SubCat別）を正しいものとした場合、合計（19,390）という数値をどのように算出したら良いか？

1については正直、It dependsというのが答えになると思います。何をもって単価差額とするのか、最も細かい粒度で数字が必要な場合は[単価差額1]、集計レベルで良いのであれば[単価差額_調整]で良いでしょう。重要なのはどちらにせよ、まずは数字の整合性について確認を行うことです。合計ベースで見ると、[単価差額1]は11,229、[単価差額_調整]は19,390、合計の差額についてもその妥当性（大きく乖離していないか等）も考える必要があるでしょう。

ここで2について考えてみると、DAX式を以下のように変更すれば対応できるようになります。

-------------------
-- Measure: [単価差額2]
-------------------
MEASURE Sales[単価差額2] = 
    SUMX(
        SUMMARIZE(
            Sales,
            dProduct[ProductCategoryName],
            dProduct[ProductSubcategoryName]
        ),
        var _actual_price = [実績単価]
        var _plan_price = [計画単価]
        var _qty = [実績数量]
        return
            IF( NOT ISBLANK( _plan_price ) && NOT ISBLANK( _actual_price ), 
            ( _plan_price - _actual_price ) * _qty
        )
    )
    FormatString = "#,0"

[単価差額1]のDAX式にあるVALUES( dProduct[ProductKey] ) を

SUMMARIZE(
Sales,
dProduct[ProductCategoryName],
dProduct[ProductSubcategoryName]
)

に変更しただけですが、このメジャーを使うことで得られる結果は以下の通りになります。合計値までしっかり正しい数字になったことが分かります。

シナリオ2のおさらいになります。

テーブルに表示された合計と、その上の項目の合計は基本的に合わない可能性があることを意識すること
合計を合わせる作業にはSUMXが使い勝手が良い
その際、どの粒度（切り口）に合わせて合計を算出したいかに留意すること
粒度が違えば計算結果も異なってくるため、自分たちが納得するビジネスロジックを考えて計算を行うこと
評価コンテキスト（Evaluation Context）を理解すること

おまけ

SUMX以外の書き方でも同じことを実現できますので、下記ご参考までに。詳細は省きますが、場合によってはこちらのほうがクエリパフォーマンスが良い場合もあるかもしれません。

---------------------------
-- Measure: [単価差額AddColumn]
---------------------------
MEASURE Sales[単価差額AddColumn] = 
    var _table = 
    FILTER(
        ADDCOLUMNS(
            VALUES( dProduct[ProductKey] ),
            "@PlanPrice", [計画単価],
            "@ActualPrice", [実績単価],
            "@ActualQty", [実績数量]
        ),
        NOT ISBLANK( [@PlanPrice] ) && NOT ISBLANK( [@PlanPrice] )
    )
    var _table2 = 
        ADDCOLUMNS(
            _table,
            "@Diff", ( [@PlanPrice] - [@ActualPrice] ) * [@ActualQty]
        )
    var _result = SUMX( _table2, [@Diff] )
    return 
        _result
    FormatString = "#,0"

合計の不一致_シナリオ3

最後の不一致シナリオはビジネスロジック（そもそも一致しないケース）になります。こちらは以前、SaaS分析の記事を紹介した際に触れていますが、DISTINCTCOUNTといったDAX関数を使用して購入顧客数を算出しようとした場合、直近12ヵ月の合計値と各月の合計は基本的に一致しないロジックとなります（下図）。

よって、最後のシナリオについてはビジネスロジックの問題であり、レポート作成者及び分析を行う人、最終ユーザーが意識的に留意すべきポイント（かつ、説明可能にすべきポイント）となります。

まとめ

DAXを使った計算では合計が一致しないことは普遍的なことです。これはDAXが間違っているのではなく、我々がしっかりとDAXの概念を理解し、ビジネスロジックに対する問いに答えられるよにすることが重要となります。

今回紹介した内容は中上級コンテンツとなりますが、評価コンテキスト（Evaluation Context）はDAXを学ぶ上で最も重要な基礎ですので、しっかり時間を取ってその概念について習得されることをお勧めします。この関門を超えることができれば、DAXを自由に扱うことができるようになり、Power BIをより使いこなせるようになるでしょう。

ダウンロード
元データ（pbix）
DAX式入りファイル（pbix）