Power BI道場 Calculateのとても重要な基礎の基礎

Power BI

Caluculateはよく使う関数です。
シェアなど比率を出すときには必須と言えます。

ですが普通の感覚と違う動きをするので理解がすごく難しいです。
わたしもなかなか理解できなくて苦労しました。

CALCULATE関数でトラップにひっかからないように解説していきます。

基礎 Calculateの内容

Calculateは次の構文です。

Sumなどの集計式の対象範囲を変えるときに使います。

CALCULATE(<評価式>,<抽出条件1>,<抽出条件2>…)

サンプルとして売上の合計金額出してみます。

赤で囲んでいる範囲の合計金額がでます。
使う式によって範囲が異なることがわかると思います。

Sum,Min,Maxなどの集計関数はレポート上で指定する

  • 抽出条件(年月)
  • 集計項目(年月、カテゴリ)

の2つによって結果が変わります。
図の式の上から順番に説明します。

  1. CALCULATEを使用しないでSUMを使用
    SUM(‘売上'[売上])
     抽出条件:年月
     出力項目:年月,カテゴリ
    とSUMは条件がすべて残ります。

    結果として年月とカテゴリ単位の売上合計がでます。
  2. CALCULATEとALLSELECTEDを使用
    CALCULATE( SUM(‘売上'[売上]) ,ALLSELECTED(‘売上’))
     抽出条件:年月
     出力項目:すべて削除
    となります。

    ALLSELECTEDはレポート上で指定した抽出条件を残します。

    集計項目は指定した項目を削除します。
    この例ではテーブルを指定したのですべての集計項目を削除です。

    結果として年月で抽出後の売上合計がでます。
  3. CALCULATEとALLを使用
    CALCULATE(SUM(‘売上'[売上]),ALL(‘売上’))
     抽出条件:無効
     出力項目:無効
    となります。

    ALLは全データを取得する関数です。
    結果として全部の売上合計がでます。

罠1: ALLSELECTEDとALLEXCEPTの違い

CALCULATEと一緒よく使うのが、次の3つの関数です。

  • ALL:すべて
  • ALLSELECTED:抽出を残し、指定の出力項目の条件を削除
  • ALLEXCEPT :指定の出力項目の集計条件を残す

ALLSELECTEDとALLEXCEPとは「削除する項目」か「削除しない項目」の指定方法の違いのようにみえます。
ですが大きい違いは抽出条件が残るか残らないかです

ALLEXEPT    = CALCULATE(SUM('売上'[金額]),ALLEXCEPT  ('売上','売上'[カテゴリ]))
ALLSELECTED = CALCULATE(SUM('売上'[金額]),ALLSELECTED('売上'[年月]))

この2つの式をみると同じように見えます。
 ALLEXEPT  : カテゴリ別の集計。
 ALLSELECTED : カテゴリ別の集計(年月以外の項目の集計のため)

ですが次のように抽出条件の年月をいれると結果が異なります。

罠2:抽出条件の設定

抽出分を設定した時の挙動について説明します。

CALCULATE(SUM('売上'[金額]),'売上'[年月] <> "2018年度")

この式の結果はどうなるでしょうか?

結果は2018年度を除外したカテゴリの集計値がでます。
予想があたった人はほとんどいないと思います。

CALCULATEで抽出項目に使った項目は集計項目の対象から外されます。

CALCULATE(SUM('売上'[金額]),ALLSELECTED('売上'[年月]) ,'売上'[年月] <> "2018年度")

と同じ意味となります。

応用 Calculateの使用例

比率を出す 例) 売上比率

計算式は次となります。

売上比率 = SUM('売上'[金額]) / CALCULATE(SUM('売上'[金額]),ALLSELECTED('売上'))

ALLSELECTEDにすることで、抽出をしても売上比率を合計1にできます。
抽出しても売上比率を変えたくない場合はALLを使用してください。

まとめ

Calculateは少し複雑な式を作るときには強力な関数です。
予想外のデータが出ることが多く理解するのが難しい関数です。

想定した値がでなくて私もCalculateを使うのを一時期避けていました。

ですが内容を理解できれば、他の方法を使うよりも簡単に計算式を作れるようになります。

Power BIの上級レベルになるにはCalculateは避けては通れない関数なので、ぜひ学んでください。


Power BIについてもっと詳しく知りたい方はPower BI記事のまとめから参照できますので、こちらもご参照ください。

  • Power BIの概要
  • Power BIの機能の使い方
  • サンプルレポートのハンズオン

についてまとめてあります。

タイトルとURLをコピーしました