BI道場

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

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

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

基礎 Calculateの内容

Calculateは次の構文です。

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

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

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

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

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

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

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

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 : カテゴリ別の集計（年月以外の項目の集計のため）

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

罠２：抽出条件の設定

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

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の機能の使い方
• サンプルレポートのハンズオン

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