Power BIのAll系関数3つの違いと使い方
Power BIのAll系の関数は、All、Allselected、Allexcepの3つがあるのですが、この違いが最初少し分かりにくいので、自分なりの解釈を紹介させて頂きます。
All系の関数は全てレポート内のフィルターを制御する関数なのですが、そもそもこのフィルター機能が、Power BIの中では結構重要な役割を担っています。
例えば、
-レポートの中で特定の対象に絞り込んだり、
-逆に絞り込まれないようにフィルターの設定を制御したり、
-絞り込む対象を細かく決めたり、
などなど、レポートのユーザーが本当に見たい情報を見せるために、フィルターを細かく制御する必要があり、そうしたケースでこれら関数は活躍します。
ですので、この関数の違いを理解し、それらを使い分けられるようになっておきましょう。
Contents
●All関数とは
まずは、この3つの中では最も理解しやすい「All関数」です。
All関数は、「全てのフィルターを解除する」機能を持つ関数です。
例外なく全てのフィルターが効かなくなります。
はっきりしていて分かりやすいですよね。
例えば下図のような、商品の売上データがあったとします。
一番右列の「売上金額」の合計を、All関数を使う場合と使わない場合で比較してみたいと思います。
●ALL使わない場合
売上（合計）= SUM(Sheet1[売上])
●ALL使う場合
売上（All） = Calculate(SUM(Sheet1[売上]),ALL(Sheet1))
そしてこの2つをレポートに表示して比較してみます。
左がALLを使わない場合で、右がALLを使った場合です。
フィルターで商品カテゴリをAに絞っていますので、ALL関数を使ってない方は商品カテゴリAの売上のみ計算され表示されています。
ところが右側のAll関数を使った計算式は、商品カテゴリのフィルターが効いてませんので、全ての商品の総計が計算されていることが分かります。
このようにAll関数というのは、指定した列 or テーブルの全てのフィルターを解除する機能を持つ関数なのです。
▶ALL関数の使い方
今回のレポートのように、ユーザーが選んだ特定の商品と、全体の総計とを比較する時に使ったり、
全体に対する売上の割合を表示したい時にも使います。
例えば今回のレポートで、商品カテゴリー毎の売上が全体に占める割合を出したい場合は、下記のように関数を書きます。
売上（割合） = Divide ('Measure'[sum], 'Measure'[All])
分母に、All関数を持ってきています。
これをレポートに表示するとこんな感じです。
商品カテゴリを選択すれば、選択された商品カテゴリが全体に占める割合が表示されます。
逆にAll関数を使わないと、商品カテゴリを選択したら、分子も分母もフィルターされてしまって、100%と表示されて思うような計算結果にならないのです。
●Allselected関数とは
All関数は全てのフィルターを取り除く関数でした。
一方こちらのAllselected関数というのは、指定したデータ・列のみ、フィルターを取り除く関数です。
All（フィルターを解除するAll関数）＋Selected（選ばれた）という感じです。
例えば下図の赤枠部分では、マトリックスビジュアルを使って、商品カテゴリ毎での商品の売上割合を表示しています。
全体に対してではなく、商品カテゴリ内での売上の割合を計算したい場合、分母に使う計算式はAllselected関数を使った下記となります。
分母=CALCULATE(SUM(Sheet1[売上]),ALLSELECTED('Sheet1'[商品]))
この場合、「商品」列にかかるフィルターのみ取り除かれ、売上の合計を出します。
マトリックスビジュアルを使う場合、「行」で使うデータ（今回は「商品カテゴリ」と「商品」列を使っている）で、自動的にフィルターがかかります。
ここで分母にAll関数を使ってしまうと、全体の売上に対する売上の割合は出せますが、商品カテゴリの総計に対する売上の割合は出せないのです。
そこで、マトリックスビジュアルの「商品」にかかっているフィルターのみを、Allselected関数であえて取り除くという処理をします。
そうすると、必然的に「商品カテゴリ」毎での総計が出せるという形です。
あまりうまく説明できない気がしますが、伝わりましたでしょうか。
Allselected関数の使い方
Power BIのマトリックスを使って、商品カテゴリ毎の分析を行う時には必須です。
詳しくは、Power BIマトリックスでカテゴリ毎での割合を計算する方法に動画付きで解説していますので、参考にしてみて下さい。
●Allexcept関数とは
Allexcept関数は、Allselected関数の逆です。
指定した列・テーブル以外の全てのフィルターを機能させるものです。
あまり使う機会はない関数ですが、概念としては覚えておいて損はないでしょう。
以上です。
ソフ蔵
IT企業でデータ分析や分析基盤構築に携わってます。
主にPower BIの使い方やデータ分析のノウハウを伝えてます。
データを使って世の中やビジネスを可視化したり、データ駆動型の組織作りの支援をしています。
データ分析・可視化基盤の構築、データ活用方法、Power BIについて相談あれば下記からお問合せ下さい！！
Power BI・データ分析を学習しよう！！
Power BIレポートのテンプレートサイトはこちらです
Power BIの入門記事・記事をピックアップしましたので、こちらも参考にしてみて下さい。
▶Power BIとは？ 記事版 / 動画版（Power BIの概要を3分で解説！）
コメントを残す