エクセル – ピボットグラフ – 値の平均線を追加する方法 (DAX・フィルター関数)

エクセル – ピボットグラフ – 値の平均線を追加する方法 (DAX・フィルター関数)

ピボットグラフへ平均値や中央値など、基本統計量の基準線を追加します。ピボットが参照しているもとデータへ列を追加せず、DAX・フィルター関数を使用し計算メジャーを作成します。

ピボットの平均値

データについて
データ

 左画像がピボットへ接続するデータです。4月1日から6月30日までの3か月間です。
・ディメンション = 日付 (1日1行)
・メジャー = 売上高

 このデータからピボットグラフを作成して売上高の平均線を追加します。

 右画像にように接続データへ列を追加して値を計算する方法は使用しません。

集計方法を「平均」にする
日付軸 棒グラフ

 データは3か月ありますが、まず、2か月にしています。
・「軸」へ「日付」
・「値」へ「売上高」を二重に設定します。
・グラフは縦棒です。

値フィールドの設定

・「売上高」のどちらかの▼三角をクリックして
・「値フィールドの設定」を選択します。

集計方法

・「集計方法」タブ
・「平均」を選択して「OK」です。

組み合わせグラフ

・グラフを組み合わせにして平均値を折線へ変更しました。
 平均値は売上高合計値と一致します。

 4月1日から5月31日の期間の平均値になるのだろうといった期待には、この方法では沿えません。

集計方法の平均とは何か
もとデータ

・売上高は日付ごとの合計です。
 すべての日が1日あたり1行です。

<平均値の計算方法>
・分子
 日付の1日ごとの売上高合計 (1行の合計)
・分母
 日付の1日ごとの売上高の行数 (1行)

軸を日付の月へ

軸を「日付」の月へ変更します。

・売上高は日付ごとの月合計です。
 すべての日が1日あたり1行です。

・4月は1日~30日なので30行
・5月は1日~31日なので31行

<平均値の計算方法>
・分子
 月の1日ごとの売上高合計 (4月なら30日間の合計)
・分母
 日付の1か月ごとの売上高の行数 (4月なら30)

期間中の日の平均売上高

 軸を月へ変更することで目的とする値へ近づいてきました。ここからさらに、軸に設定している月 (2か月)をまとめた軸へ変更すれば平均値を求めることができそうです。

 4月1日~5月31日の期間の日の平均売上高は
・分子
 4月1日~5月31日の期間の売上高合計
・分母
 4月1日~5月31日の日=61

軸はあくまでも日付の日

 しかし、グラフの軸は「日付」の日です。
 軸が「日付」の日の状態のまま、軸が「日付」の月の2か月合計の線をグラフへプロットします。

フィルター関数 ALLSELECTED

CALCULATE
=CALCULATE(平均値を求める式,フィルター式)

 関数「CALCULATE」はフィルター式の定義に従い平均を求める式 (テーブル式) の計算結果を返します。

#平均値を求める式
AVERAGE([売上高])


・平均を求める式は「AVERAGE([売上高])」
 売上高の平均値を求めます。この式が返す値は「日付」の日ごとの平均値です。つまり、はじめに説明した値、「集計方法平均」と同一値です。

ALLSELECTED
#フィルター式
ALLSELECTED(テーブル,フィルター保持しないカラム)

・フィルター式は関数「ALLSELECTED」です。
 括弧の中へ保持しないフィルターを定義します。
 「ALLSELECTED()」 このように括弧の中を省略するとすべてのフィルターが適用されます。

・テーブル
 フィルター保持しないカラムがあるテーブル名を指定します。省略できます。
 今回のデータは1テーブルなので省略します。

・フィルター保持しないカラム
 フィルター保持しないカラム名を指定します。複数指定できます。
 今回は「ALLSELECTED()」でもできるのですが、月別に平均値が計算できるように「日付」を設定します。

#DAX式
=CALCULATE(AVERAGE([売上高]),ALLSELECTED(sales[日付]))


 これで「日付」のフィルターを保持せず (軸の日付を無視して) 「売上高」の平均値を算出することができます。

 「日付」のフィルターを保持しないということは、グラフの軸を「日付」に設定しても軸を無視して計算するということです。

グラフ作成
新規メジャー作成

 計算メジャーを新規作成します。

組み合わせグラフ

・作成した計算メジャーを「値」へ設定
・「組み合わせグラフ」で「平均値」を折線にします。
 第2軸に設定する必要はありません。

完成

 平均値が一本線になっていれば成功です。

月ごとの平均線
月ごと平均

 「日付」の月ごとに平均線を変えるときは、「日付 (月)」を軸へ設定します。「日付」の上へ設定するとグラフの軸ラベルが見やすくなります。

・「日付」の日はフィルターされない
・「日付」の月はフィルターされる
 フィルター関数で保持しないフィルターを「日付」だけに定義しているので、軸に設定している「日付 (月)」が保持される結果、月での集計が可能になります。

その他の基本統計量

百分位
百分位
=CALCULATE(百分位求める式,ALLSELECTED(sales[日付]))

 平均値を求めるDAXをコピペして「平均値を求める式」の部分を「百分位を求める式」へ変更します。

#百分位を求める式
PERCENTILE.EXC(sales[売上高],0.75)

 「0.75」の値を変更してください。「0.5」が中央値です。
 「1.0」>値>「0.0」の範囲で変更できます。「1.0」と「0.0」は入力できません。

最大値・最小値
MAX、MIN

 関数「PERCENTILE.EXC」が「1.0」と「0.0」を受け付けないので
・最大値「MAX」
・最小値「MIN」

グラフ作成
基本統計量線

 それぞれの計算メジャーを「値」へ設定します。

もとデータへ列を追加しない理由

当月の値 (列を追加する方法)
当月

 画像のように当月の値を表示したい。

データ

 画像のように列を追加します。

選択期間の値 (列を追加する方法)
選択期間

 選択した期間、あるいは、全期間の値を見たい。

期間の組み合わせ

 期間を組み合わせた列を追加します。
 データ期間が7月、8月、9月と追加されるたびに列を追加します。

 計算メジャーを作成しておけばデータを追加しても列を追加する必要がなくなります。

データを追加する方法
行を追加

・もとデータの下へ翌月のデータをコピペします。
・「データ」タブ
・「すべて更新」をクリックします。

ピボット

 追加したデータへ接続できていれば成功です。