エクセル – ピボットグラフ – 値の平均線を追加する方法 (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」は入力できません。
最大値・最小値
関数「PERCENTILE.EXC」が「1.0」と「0.0」を受け付けないので
・最大値「MAX」
・最小値「MIN」
グラフ作成
それぞれの計算メジャーを「値」へ設定します。
もとデータへ列を追加しない理由
当月の値 (列を追加する方法)
画像のように当月の値を表示したい。
画像のように列を追加します。
選択期間の値 (列を追加する方法)
選択した期間、あるいは、全期間の値を見たい。
期間を組み合わせた列を追加します。
データ期間が7月、8月、9月と追加されるたびに列を追加します。
計算メジャーを作成しておけばデータを追加しても列を追加する必要がなくなります。
データを追加する方法
・もとデータの下へ翌月のデータをコピペします。
・「データ」タブ
・「すべて更新」をクリックします。
追加したデータへ接続できていれば成功です。
