CALCULATE関数で条件付きの合計や平均(DAX関数)

CALCULATE関数 条件 合計 平均 複数条件DAX関数
スポンサーリンク

PowerBIでExcelのSUMIF、AVERAGEIF、COUNTIF関数にあたる「条件を満たす場合の合計、平均、カウント」の方法を紹介します。

DAX関数はExcel関数とは異なり、SUMIFやAVERAGEIFS関数などがありません。 CALCULATE関数を用いて複数条件を指定して、同様の計算が可能です。

CALCULATE関数はPowerBIのDAX関数の中で一番と言ってよいほど重要ですので、ぜひご参考下さい!

条件を満たした合計/平均/カウント(SUMIF/AVERAGEIF/COUNTIF)
複数条件を満たした合計/平均/カウント(SUMIFS/AVERAGEIFS/COUNTIFS)
をDAX関数であるCALCULATE関数を用いて具体的に紹介します!

CALCULATE関数で条件付きの合計

PowerBIのDAX関数でSUMIFやSUMIFS関数的なことをする方法は
「SUM関数」+「CALCULATE関数」を使います。

CALCULATE関数の式にSUM関数を入れてやれば条件付きの計算ができます!

【SUM関数】構文
 =SUM(<column>)

・column:列を指定
 合計したい列を指定

【CALCULATE関数】構文
 =CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])

つまり
 
= CALCULATE(式,フィルター①,フィルター②,…)
とフィルター箇所に複数条件を追加して計算させる関数

参考:Microsoft Docs CALCULATE関数

つまり、CALCULATE関数の式にSUM関数を入れるだけでOK!
例えば下記の構文なら、製品列がお菓子の場合の売上合計を計算するメジャーになります。
=CALCULATE(SUM(’売上列),’製品列=”お菓子”)

【参考記事】
SUMX、SUMIFS関数で条件付きの合計
▶FILTER関数でテーブルを抽出し計算、CALCULATE関数との組み合わせ
▶PowerBIで条件付きカウント(CALCULATE関数/COUNT関数)
▶(応用)PowerBIでパレート図の作成(テンプレコードあり)

スポンサーリンク

SUMIF 一つの条件を満たす合計

SUMIFにあたる「一つの条件を満たす合計」を具体的に説明していきます。

◆使用するデータ
 下記のような製品別の売上リストをPowerBIに取り込んでいる場合を想定します。

ここに新しいメジャーで「性別が女性である売上」を追加します。
「新しいメジャー」をクリックしメジャーを追加

PowerBI メジャー追加

メジャー名:女性の売上合計
関数:CALCULATE関数、SUM関数
CALCULATE(式,フィルター①,フィルター②,…)

CALCULATE関数の式の部分にSUM関数を入れ込めばOKなので、

女性の売上合計 = CALCULATE(SUM(‘購入リスト'[売上]),’購入リスト'[性別]=”女性”)

CALCULATE関数 SUM 条件 合計 SUMIF

これで、購入者が女性である売上の合計をメジャーで追加できました!
レポート画面でカードを追加して、追加したメジャーの値を確認します。

CALCULATE関数 SUM関数 合計 条件

ちなみに、通貨(¥マーク)の書式設定は、
下記の「メジャーツール」タブ→「書式」→「通貨」
$→\に変更で設定できます!

◆関連記事
▶「メジャー」「新しい列」を使ったデータ加工方法

これで、Excelの、SUMIF関数と同じこともDAX関数でできました!
CALCULATE関数を使えば、好きな条件を指定できます!

スポンサーリンク

SUMIFS 複数条件を満たす合計

次に、「複数条件を満たす場合の合計」を具体的に説明していきます。

先ほどと同様にメジャーを追加し、性別が女性で地域が関東の売上合計を設定していきます。

メジャー名:女性+関東の売上合計
関数:CALCULATE関数、SUM関数
CALCULATE(式,フィルター①,フィルター②,…)

CALCULATE関数のフィルターを複数設定するだけです!

女性+関東の売上合計 = CALCULATE(SUM(‘購入リスト'[売上]),’購入リスト'[性別]=”女性”,’購入リスト'[地域]=”関東”)

SUM CALCULATE関数 複数条件 SUMIFS関数

これで、購入者が女性かつ、地域が関東の売上合計をメジャーで追加できました!
同様にレポート画面で確認します。条件をプラスで追加しているので、もちろん数が少なくなっていますね!

CALCULATE関数 SUM関数 複数条件 SUMIFS

これで、SUMIFS関数と同じこともDAX関数でできました!CALCULATE関数で囲むだけなので、簡単ですね!

スポンサーリンク

CALCULATE関数で条件付きの平均

PowerBIのDAX関数でAVERAGEIFやAVERAGEIFS関数的なことをする方法は
「AVERAGE関数」+「CALCULATE関数」を使います。

AVERAGE関数、AVERAGEX関数などの詳しい説明は下記をご参考下さい!

▶メジャーで平均値の追加(AVERAGE/AVERAGEX関数

先ほどと同様に、CALCULATE関数で囲むだけです!使い方は全く一緒です!

AVERAGEIF 一つの条件を満たす平均

実際に、「一つの条件を満たす平均」を具体的に説明していきます。

先ほどと同様にメジャーを追加し、性別が女性の売上平均を設定していきます。

メジャー名:女性の売上平均
関数:CALCULATE関数、AVERAGE関数
CALCULATE(式,フィルター①,フィルター②,…)

CALCULATE関数の式にAVERAGE関数を入れるだけ!

女性の売上平均 = CALCULATE(AVERAGE(‘購入リスト'[売上]),’購入リスト'[性別]=”女性”)

平均 CALCULATE関数 AVERAGE

これで、購入者が女性の売上平均をメジャーで追加できました!
レポート画面で確認すると、平均値が追加できています!

CALCULATE関数 AVERAGE関数 条件 平均

これで、AVERAGEIF関数と同じこともDAX関数でできました!

スポンサーリンク

AVERAGEIFS 複数条件を満たす平均

次に、AVERAGEIFSに相当する「複数条件を満たす平均」を具体的に説明していきます。

同様にメジャーを追加し、性別が女性で地域が関東の売上平均を設定していきます。

メジャー名:女性+関東の売上平均
関数:CALCULATE関数、AVERAGE関数
CALCULATE(式,フィルター①,フィルター②,…)

CALCULATE関数のフィルターを複数設定するだけ!

女性+関東の売上平均 = CALCULATE(AVERAGE(‘購入リスト'[売上]),’購入リスト'[性別]=”女性”,’購入リスト'[地域]=”関東”)

AVERAGE CALCULATE関数 複数条件 AVERAGEIFS

これで、購入者が女性かつ、地域が関東の売上平均をメジャーで追加できました!
レポート画面で確認すると、こちらも追加できています!

CALCULATE関数 AVERAGE 複数条件  AVERAGEIFS

これで、AVERAGEIFS関数と同じこともDAX関数でできました!
CALCULATE関数で囲むだけで、簡単ですね!

スポンサーリンク

CALCULATE関数で条件付きのカウント

PowerBIのDAX関数でCOUNTIFやCOUNTIFS関数的なことをする方法は
「COUNT関数」+「CALCULATE関数」を使います。

条件付きのカウント方法は下記で詳しく説明しているので、ぜひご参考下さい。 同様に、CALCULATE関数で囲むだけですね!!

▶DAX関数で条件付きのカウント(COUNT関数まとめ/CALCULATE関数/FILTER関数)

スポンサーリンク

PowerBIでSUMIF/AVERAGEIF/COUNTIFまとめ

今回のまとめ
ExcelのSUMIF、AVERAGEIF、COUNTIFはPowerBIのDAX関数には無い
各関数をCALCULATE関数で囲むだけで代用可能!
複数条件はCALCULATE関数の条件を追加するだけ!

CALCULATE関数は非常に重要なので、ぜひ使い方をマスターしておきましょう!

今回同様に下記でPowerBIの便利機能とDAX関数をまとめています。 ぜひご参考下さい!

【PowerBI】便利機能9選とDAX関数まとめ

よく使うDAX関数まとめ

最後まで読んでいただきありがとうございました!

記事が良ければ☟クリック応援よろしくお願いします!!

にほんブログ村 サラリーマン日記ブログ IT系サラリーマンへ
にほんブログ村

コメント

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