ついにPower BIで移動平均を出すための関数が登場！WINDOW関数とは？
目次
Power BIはデータをモデリングするために必要となる関数が覚えきれないほど準備されています。
今回はテーブル内の特定の行や列のサブセットに対してさまざまな計算を実行するためのWINDOW関数を紹介します。
WINDOW関数を聞いたことはあるが使い方がわからない、また、移動平均など期間の範囲を指定した計算をしたいが、どうやったらいいかわからないといった疑問はないでしょうか。
WINDOW関数とは？
WINDOW関数の基本的な機能は、ユーザーが定義した範囲のテーブル内の行または列の値に基づいて移動平均やトレンドなどの計算をすることができます。
このWINDOW関数ですが、数々のアップデートやバージョンがリリースされるにつれて、関数の構文や機能が拡張・改善されてきました。
最近ではデータのサブセットに対してより複雑な計算や傾向分析を実行できる新しいDAX関数と機能が導入され、さらには速度や精度を犠牲にすることなくより大きなデータセットを処理できるなど、パフォーマンスの向上が図られています。
WINDOW関数で実行できる計算は以下のようなものがあります
WINDOW関数で実行できること
集計と累積集計： WINDOW関数は、行の範囲を定義することで、範囲内の合計や累積の合計を計算できます。
移動平均とトレンド ：行の範囲を定義することで、移動平均またはトレンドを計算することができます。
順位とパーセンタイル：表内のすべての行または特定のサブセットを含む行の範囲を定義することにより、表内のデータのランク付けやパーセンタイルの算出ができます。
グループ化および集計：特定の列の特定の値または値の組み合わせを持つすべての行を含む行の範囲を定義することにより、テーブル内のデータをグループ化および集計ができます。
これらはWINDOW 関数でできることのほんの一例に過ぎません。Power BIで利用できる他のDAX関数や機能と組み合わせることで、WINDOW関数の機能をさらに拡張することができます。
WINDOW関数の使い方
WINDOW関数の詳細は以下のリンク先に記載されています。ここでは簡単に説明と補足をさせていただきます。
https://learn.microsoft.com/ja-jp/dax/window-function-dax
WINDOWS関数の構文は以下の通りです。
WINDOW (
from,
[from_type],
to,
[to_type],
[],
[],
[],
[]
)
それではそれぞれの引数の説明をします。
from：移動平均など計算したい範囲の開始位置を示します。スカラー値を返す任意のDAX式を指定することができます。移動平均値を求めたい行の位置よりも前の行であれば負の値、後ろの行であれば正の値になります。
from_type：移動平均など、計算したい範囲の開始位置のタイプ選びます。指定できる値は、ABS（絶対位置）、REL（相対位置）で、デフォルトはREL（相対位置）となります。REL（相対位置）の場合、移動平均値を求めたい行の位置からの相対位置となります。
to：fromと同じですが、fromは計算したい範囲の開始位置でしたが、toは計算したい範囲の終了位置を表します。
to_type：と同じですが、toがABS（絶対位置）またはREL（相対位置）かを選択します。fromと同様、デフォルトはREL（相対位置）となります。
＜relation＞：移動平均などの計算の基となるテーブル式です。省略も可能です。
＜orderBy＞：ソート方法を定義します。
＜blanks＞：並べ替えの際に空白をどのように処理するかを定義します。デフォルトはKEEPです。
＜partitionBy＞：のパーテションを定義します。デフォルトはパーテションされません。
WINDOW関数の使用方法については以上です。ここでは実際にWINDOW関数を使用しますので、使用方法はそちらでもご理解いただけると思います。
それではWINDOW関数を使用して移動平均を計算してみましょう。
WINDOW関数を使って、移動平均を計算しよう。
WINDOW関数を使用して、①売上の移動平均、➁商品グループ毎の移動平均の計算を実行します。
今回はWINDOW関数の使用方法をわかりやすくするため、データと移動平均の計算は簡易的な内容となっております。
↓Date（日付）、商品グループ、商品ID、売上の列をもったテーブルを使用します。以降、以下のテーブルは”Sales”と記載させていただきます。
①売上の移動平均
まず、売上の移動平均を算出します。今回使用する列は‘Date’と‘売上’です。
↓ゴールは表のとおり、‘Date’列と‘売上’列から移動平均を求めます。
例えば以下の赤枠の移動平均’1,800’を求めるためには ①(1,400)＋➁(2,200)を行数で割ります。
操作手順は
“新しいメジャー”→以下の記述を入力します。
移動平均 = CALCULATE(
AVERAGE(Sales[売上]),
WINDOW(-1,REL,0,REL,
ALL(Sales[Date])
)
)
↑記述はCALCULATE関数内に引数として、AVERAGE関数とWINDOW関数を渡しています。
移動平均の計算が目的なのでAVERAGE関数を使用しております。AVERAGE関数内には引数としてテーブルの“売上”列を渡しています。WINDOW関数は2日（前日と当日）移動平均の売上を計算するため、WINDOW関数の引数は-1、は0となります。とは移動平均を算出したい各行から相対的に範囲を選択するのでともにREL(相対位置)を選択します。は“Date”を選択します。ほかの引数は入力せず、デフォルトとします。
作成した移動平均のメジャーを表で確認すると以下の表のとおり計算が実行されていることがわかります。
↓2023年4月2日の移動平均は1,800に対し、2023年4月1日～2日の平均値は1,800ですので、狙い通り計算できていますね。
➁商品グループ毎の移動平均
次は商品グループ毎の移動平均を計算していきましょう。
手順は①売上の移動平均と同じですが、商品グループ毎の情報が必要になりますので、 ‘商品グループ’の列の追加と計算への反映をしなければなりません。
↓ゴールは表のとおり、移動平均（’移動平均2’列）が商品グループ毎に算出します。
例えば以下の赤枠の移動平均’2,300’を求めるためには ①(2,200)＋➁(2,400)を行数で割ります。
商品グループ毎の移動平均は以下の記述となります。①売上の移動平均で作成したメジャーと区別するため、メジャーの名称は“移動平均２”とさせていただきます。
移動平均２ = CALCULATE(
AVERAGE(Sales[売上]),
WINDOW(-1,REL,0,REL,
ALL(Sales[Date],Sales[商品グループ]),
ORDERBY(Sales[Date],ASC),
PARTITIONBY(Sales[商品グループ])
)
)
今回は先ほど算出した①売上の移動平均を’商品グループ’毎の移動平均にするため、WINDOW関数に商品グループの列をインプットする必要があります。また、WINDOW関数の引数のORDERBYはPARTITIONBY関数使用のため追加しております。そのほかは①売上の移動平均と同じ記述です。
↓作成した移動平均２のメジャーを表で確認すると以下の表のとおり計算が実行されていることがわかります。
商品グループ列の“A”、2023年4月4日の移動平均2が2,300に対し、2023年4月1日と2023年4月４日の平均が2,300となっていますね。狙い通り移動平均の算出ができています。
WINDOW関数を使用し、①売上の移動平均、➁商品グループ毎の移動平均の計算をしてみました。
今回は移動平均の算出でAVERAGE関数を使用しましたが、WINDOW関数を使用して累積などを計算する場合はほかの関数に置き換えて使用してみてください。
また、fromとtoも同様に移動平均や累積などの計算のさいには目的に合った数値に置き換えて使用してください。
Power BIではWINDOW関数を他の機能で代替することが可能ですが、複雑な処理が必要となります。今回のWINDOW関数の使用例からWINDOW関数は非常に簡単な記述で計算ができています。
まとめ
- WINDOW関数は行の範囲を定義することで、累積集計や移動平均、トレンドなど複雑な記述が必要なく計算ができます。
- WINDOW関数を使用して、①売上の移動平均、➁商品グループ毎の移動平均の算出を実行しました。WINDOW関数は引数に行の範囲、計算に使用する列・テーブル、ソート、パーティションなどの情報を渡すだけで複雑な計算が簡単なプロセスで実行できます。
＜Power BIハンズオンセミナー＞
弊社ではPower BIをはじめとするさまざまな無料オンラインセミナーを実施しています！
＞＞セミナー一覧はこちら
＜Power BIの導入支援＞
弊社ではPower BIの導入支援を行っています。ぜひお気軽にお問い合わせください。
＞＞Power BIの導入支援の詳細はこちら
＜PowerBIの入門書を発売中！＞
弊社ではPower BIの導入から基本的な使い方・活用方法の基礎などをわかりやすく解説した書籍も販売しています。
＞＞目次も公開中！書籍の詳細はこちら
