Caluculateはよく使う関数です。
シェアなど比率を出すときには必須と言えます。
ですが普通の感覚と違う動きをするので理解がすごく難しいです。
わたしもなかなか理解できなくて苦労しました。
CALCULATE関数でトラップにひっかからないように解説していきます。
基礎 Calculateの内容
Calculateは次の構文です。
Sumなどの集計式の対象範囲を変えるときに使います。
CALCULATE(<評価式>,<抽出条件1>,<抽出条件2>…)
サンプルとして売上の合計金額出してみます。
赤で囲んでいる範囲の合計金額がでます。
使う式によって範囲が異なることがわかると思います。
Sum,Min,Maxなどの集計関数はレポート上で指定する
- 抽出条件（年月）
- 集計項目（年月、カテゴリ）
の２つによって結果が変わります。
図の式の上から順番に説明します。
- CALCULATEを使用しないでSUMを使用
SUM(‘売上'[売上])
抽出条件：年月
出力項目：年月,カテゴリ
とSUMは条件がすべて残ります。
結果として年月とカテゴリ単位の売上合計がでます。 - CALCULATEとALLSELECTEDを使用
CALCULATE( SUM(‘売上'[売上]) ,ALLSELECTED(‘売上’))
抽出条件：年月
出力項目：すべて削除
となります。
ALLSELECTEDはレポート上で指定した抽出条件を残します。
集計項目は指定した項目を削除します。
この例ではテーブルを指定したのですべての集計項目を削除です。
結果として年月で抽出後の売上合計がでます。 - 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 : カテゴリ別の集計（年月以外の項目の集計のため）
ですが次のように抽出条件の年月をいれると結果が異なります。
罠２：抽出条件の設定
抽出分を設定した時の挙動について説明します。
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の機能の使い方
- サンプルレポートのハンズオン
についてまとめてあります。
