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(式,フィルター①,フィルター②,…)
とフィルター箇所に複数条件を追加して計算させる関数
つまり、CALCULATE関数の式にSUM関数を入れるだけでOK！
例えば下記の構文なら、製品列がお菓子の場合の売上合計を計算するメジャーになります。
＝CALCULATE(SUM（’売上列）,’製品列＝”お菓子”）
【参考記事】
▶SUMX、SUMIFS関数で条件付きの合計
▶FILTER関数でテーブルを抽出し計算、CALCULATE関数との組み合わせ
▶PowerBIで条件付きカウント(CALCULATE関数/COUNT関数)
▶(応用)PowerBIでパレート図の作成(テンプレコードあり)
SUMIF 一つの条件を満たす合計
SUMIFにあたる「一つの条件を満たす合計」を具体的に説明していきます。
◆使用するデータ
下記のような製品別の売上リストをPowerBIに取り込んでいる場合を想定します。
ここに新しいメジャーで「性別が女性である売上」を追加します。
「新しいメジャー」をクリックしメジャーを追加
メジャー名：女性の売上合計
関数：CALCULATE関数、SUM関数
＝CALCULATE(式,フィルター①,フィルター②,…)
CALCULATE関数の式の部分にSUM関数を入れ込めばOKなので、
女性の売上合計 = CALCULATE(SUM(‘購入リスト'[売上]),’購入リスト'[性別]=”女性”)
これで、購入者が女性である売上の合計をメジャーで追加できました！
レポート画面でカードを追加して、追加したメジャーの値を確認します。
ちなみに、通貨(￥マーク)の書式設定は、
下記の「メジャーツール」タブ→「書式」→「通貨」
$→\に変更で設定できます！
これで、Excelの、SUMIF関数と同じこともDAX関数でできました！
CALCULATE関数を使えば、好きな条件を指定できます！
SUMIFS 複数条件を満たす合計
次に、「複数条件を満たす場合の合計」を具体的に説明していきます。
先ほどと同様にメジャーを追加し、性別が女性で地域が関東の売上合計を設定していきます。
メジャー名：女性＋関東の売上合計
関数：CALCULATE関数、SUM関数
＝CALCULATE(式,フィルター①,フィルター②,…)
CALCULATE関数のフィルターを複数設定するだけです！
女性＋関東の売上合計 = CALCULATE(SUM(‘購入リスト'[売上]),’購入リスト'[性別]=”女性”,’購入リスト'[地域]=”関東”)
これで、購入者が女性かつ、地域が関東の売上合計をメジャーで追加できました！
同様にレポート画面で確認します。条件をプラスで追加しているので、もちろん数が少なくなっていますね！
これで、SUMIFS関数と同じこともDAX関数でできました！CALCULATE関数で囲むだけなので、簡単ですね！
CALCULATE関数で条件付きの平均
PowerBIのDAX関数でAVERAGEIFやAVERAGEIFS関数的なことをする方法は
「AVERAGE関数」＋「CALCULATE関数」を使います。
AVERAGE関数、AVERAGEX関数などの詳しい説明は下記をご参考下さい！
▶メジャーで平均値の追加(AVERAGE/AVERAGEX関数
先ほどと同様に、CALCULATE関数で囲むだけです！使い方は全く一緒です！
AVERAGEIF 一つの条件を満たす平均
実際に、「一つの条件を満たす平均」を具体的に説明していきます。
先ほどと同様にメジャーを追加し、性別が女性の売上平均を設定していきます。
メジャー名：女性の売上平均
関数：CALCULATE関数、AVERAGE関数
＝CALCULATE(式,フィルター①,フィルター②,…)
CALCULATE関数の式にAVERAGE関数を入れるだけ！
女性の売上平均 = CALCULATE(AVERAGE(‘購入リスト'[売上]),’購入リスト'[性別]=”女性”)
これで、購入者が女性の売上平均をメジャーで追加できました！
レポート画面で確認すると、平均値が追加できています！
これで、AVERAGEIF関数と同じこともDAX関数でできました！
AVERAGEIFS 複数条件を満たす平均
次に、AVERAGEIFSに相当する「複数条件を満たす平均」を具体的に説明していきます。
同様にメジャーを追加し、性別が女性で地域が関東の売上平均を設定していきます。
メジャー名：女性＋関東の売上平均
関数：CALCULATE関数、AVERAGE関数
＝CALCULATE(式,フィルター①,フィルター②,…)
CALCULATE関数のフィルターを複数設定するだけ！
女性＋関東の売上平均 = CALCULATE(AVERAGE(‘購入リスト'[売上]),’購入リスト'[性別]=”女性”,’購入リスト'[地域]=”関東”)
これで、購入者が女性かつ、地域が関東の売上平均をメジャーで追加できました！
レポート画面で確認すると、こちらも追加できています！
これで、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関数をまとめています。 ぜひご参考下さい！
最後まで読んでいただきありがとうございました！
記事が良ければ☟クリック応援よろしくお願いします！！
コメント