エクセルピボット 計算の種類 DAX関数式_11_比率の累計

公開日 2023年2月19日 最終更新日 2023年2月19日

データについて

<無料> 建築着工統計調査_表1_用途別_全国_月次 (列 – 複数値形式)

政府統計の総合窓口(e-Stat)(https://www.e-stat.go.jp/
「建築着工統計調査」(国土交通省)を基にデータ製作所が作成

データをダウンロードしたページ

本稿の定義

・データテーブル : ピボットで集計する前のもとデータ
・ディメンション : 「日付」「地域」「用途」
・メジャー : 「床面積の合計 (㎡)」「建築物の数 (棟)」「工事予定額 (万円)」
 メジャーは「建物の数 (棟)」だけを使用しています。

データテーブル

コンテキストフィルターが適用されていない : 画像の左側のピボット
このように定義します。
「行」「列」ともにコンテキストがなく、メジャーを集計 (合計) している状態です。

・コンテキストフィルターが適用されている : 画像右側のピボット
 行は「用途」の「A住居専用住宅」「B住居専用準住宅」で、「日付 (年)」は「2020」「2021」「2022」のコンテキストフィルタが適用されている。
 列は「建築主」の「国」「市区町村」「都道府県」のコンテキストフィルターが適用されている。

・せルの値 : ピボットの行列がクロスしたメジャー集計値。画像でH4の値 (セルの値) は171です。

 コンテキストフィルターが適用されているされているH4のセルの値とは、
*「用途」=「A住居専用住宅」AND「日付 (年)」=「2020」AND「建築主」=「国」
 このコンテキストフィルター条件を満たすメジャーの集計 (合計) 値といえます。

・ディメンションフィルター : ディメンションのいくつかの項目を選択している状態
 ピボットは「用途」「日付 (年)」「建築主」でディメンションフィルターを適用しています。
 本稿は、常にディメンションフィルターを適用していることを前提し説明しています。説明の都度にディメンションフィルターについて記述していません。
 なお、メジャーフィルターは適用していません。

本投稿は、このような定義と、画像右側のピボットの変化をもとに解説しています。

計算の種類の前段階である集計方法は
「合計」です。
「SUM」「SUMX」のような合計値を返す集計関数を使用するという意味です。

<イメージ>
 ある会社には複数の部があります。
・ディメンションフィルター : 複数の部から営業部を選ぶ。

・コンテキストフィルター : 営業部員の性別や苗字や出身地のデータテーブルから、クロス集計表をつくる。
 行列がクロスするセルへは、男性・佐藤さん・東京出身のようなコンテキストフィルター条件を満たす値だけが入る 。女性・佐藤さん・東京出身は、その条件を満たす別のセルへ値が入る。

・メジャー集計 (合計) : 男性・佐藤さん・東京出身が2名いたら、2名の身長 (メジャー) を合計して該当するセルへ値を返す。

計算の種類 比率の累計

 「比率の累計」を選択すると、基準フィールドを選択することができます。ここでは「日付 (年)」を選択して説明します。

 行われる計算は
 「日付 (年)」に従った [累計値 / 列の親集計] です。列の親集計とは最終累計値、画像のピボットでいうと2022年まで累計した値です。つまり、「日付 (年)」の親 (上位階層) が「用途」ですから、「A居住専用住宅」と「B居住専用準住宅」ごとの合計値が列の親集計のことです。。

DAX関数式

 先ず、累計計算のために今の値を得ます。
 メジャー「建物の数 (棟)」を合計するとセルの値を得ることができます。

=SUM('データテーブル'[建築物の数 (棟)])

 集計結果は画像左側のピボットの値と一致します。

 次に、過去の値を計算します。

[今の値+前の値+前前の値+前前前の値・・・+初めの値] を計算します。
 実は、スカッとした計算式がわかりません。とうことで、一応は記事にしているものの良いとは思えない計算式を記載してあります。

 まず、前年の値を集計して返します。

 現在のピボットの行は「用途」「日付 (年)」、列は「建築主」のコンテキストフィルターが適用されています.。
 現在の、行と列のコンテキストフィルターのうち、行に設定されている「日付 (年)」のコンテキストフィルター無視することで前年のメジャーが集計できます。

 ただし、日付を年でグループ化しているので話がややこしくなります。

=CALCULATE(SUM('データテーブル'[建築物の数 (棟)]),
DATEADD(PREVIOUSYEAR('データテーブル'[日付]),0,year)
,ALLSELECTED('データテーブル'[日付 (年)]))

 タイムインテリジェンス関数で使用できるパラメーター (値) は「日付」だけです。ところがピボットは「日付」を「日付 (年)」でグループ化しています。「日付 (年)」とは日付という数値ではなく、文字列です。
 ということで、日付でメジャーを集計しながら文字列のコンテキストへ値を返すという技が要求されます。

CALCULATE : フィルター条件付きで「[建築物の数 (棟)]」の合計を集計するフィルター関数

DATEADD : 現在のコンテキストの日付から、前年にシフトした日付の値を返すタイムインテリジェンス関数
 「現在のコンテキストの日付から」ということは、例えばデータテーブルの最終の日付が11月30日のとき、前年は、前前年の12月1日から前年の11月30日になります。
 そうなると、2022年の前年のメジャーは2020年12月1日~2021年11月30日の集計になります。つまり、テータテーブルの最終日付次第で1年が動的に定義されます。一方で「日付 (年)」は文字列ですから静的です。
 通常の1年は1月1日~12月31日のセンチュリーイヤーです。そこでPREVIOUSYEARを入れ子にして、1年とは1月1日~12月31日であると定義します。

PREVIOUSYEAR : データの最終日の基づいて前年の値を集計するタイムインテリジェンス関数
 年末の規定値が12月31日ですから、この関数でデータテーブルの日付の最終日が何時だろうが1年とは1月1日~12月31日だ!ということになります。これでDATEADDの動的課題を解決できます。
 ちなみに、PREVIOUSYEARは前年の1月1日~12月31日のメジャーを集計するのだからDATEADDのパラメーターは「0」(当年) です。「-1」にすると前前年になります。

ALLSELECTED : 現在の、行で使用されている「日付 (年)」のコンテキストフィルターを無視するフィルター関数

左側のピボットのセルB4は、
「用途」=「A住居専用住宅」AND「日付 (年)」=「2020」AND「建築主」=「国」
 このコンテキストフィルター条件を満たすメジャーの集計 (合計) 値です。
 セルB4と同様にセルB5の値もセルB6の値も、すべてのセル値は、それぞれが、それぞれのコンテキストフルタ―条件を満たすメジャーの集計 (合計) 値です。

・「用途」=「A住居専用住宅」を適用する
・「日付 (年)」=「2020」を無視する
・「建築主」=「国」は適用する

 各セルに適用されている行のコンテキストフィルターのうち「日付 (年)」を無視して前年の値を得る。列の「用途」、前年へシフトした「日付 (年)」と行「建設主」に従って値を返せば、すべてのセルは前年の値になります。

 前の値を返すことができます。ただ、[前の値+今の値] の計算式では
・2021年のセルには [2020年の値 + 2021年の値] これは累計計算として成功です。
・2022年のセルのは [2021年の値 + 2022年の値] これは失敗です。2020年の値が足りません。
 [前の値+今の値] の計算式の結果は移動計算になります。

 そうなると、
[今の値+前の値+前前の値+前前前の値・・・+初めの値] を計算します。
 説明しているピボットの日付は3年間だから前前前年は不要ですが、一応、こんな感じということで記載しています。

=CALCULATE(SUM('データテーブル'[建築物の数 (棟)]),
DATEADD(PREVIOUSYEAR('データテーブル'[日付]),0,year)
,ALLSELECTED('データテーブル'[日付 (年)]))
+CALCULATE(SUM('データテーブル'[建築物の数 (棟)]),
DATEADD(PREVIOUSYEAR('データテーブル'[日付]),-1,year)
,ALLSELECTED('データテーブル'[日付 (年)]))
+CALCULATE(SUM('データテーブル'[建築物の数 (棟)]),
DATEADD(PREVIOUSYEAR('データテーブル'[日付]),-2,year)
,ALLSELECTED('データテーブル'[日付 (年)]))
+[セルの値]

PREVIOUSYEAR : パラメータを変更して「日付 (年)」をどんどん遡る。

=CALCULATE(SUM('データテーブル'[建築物の数 (棟)]),
DATEADD(PREVIOUSYEAR('データテーブル'[日付]),0,year)
,ALLSELECTED('データテーブル'[日付 (年)]))
+CALCULATE(SUM('データテーブル'[建築物の数 (棟)]),
DATEADD(PREVIOUSYEAR('データテーブル'[日付]),-1,year)
,ALLSELECTED('データテーブル'[日付 (年)]))
+CALCULATE(SUM('データテーブル'[建築物の数 (棟)]),
DATEADD(PREVIOUSYEAR('データテーブル'[日付]),-2,year)
,ALLSELECTED('データテーブル'[日付 (年)]))
+SUM('データテーブル'[建築物の数 (棟)])

 分母になる列の親集計の値を得ます。
 現在のピボットの行は「用途」「日付 (年)」、列は「建築主」のコンテキストフィルターが適用されています.。
 現在の、行と列のコンテキストフィルターのうち、列に設定されている「日付 (年)」のコンテキストフィルター無視することで列の親集計を算出できます。

=CALCULATE(SUM('データテーブル'[建築物の数 (棟)])
,ALLSELECTED('データテーブル'[日付 (年)]))

CALCULATE : フィルター条件付きで「[建築物の数 (棟)]」の合計を集計するフィルター関数

ALLSELECTED : 現在の、列で使用されている「日付 (年)」のコンテキストフィルターを無視するフィルター関数

 左側のピボットのセルB4は、
「用途」=「A住居専用住宅」AND「日付 (年)」=「2020」AND「建築主」=「国」
 このコンテキストフィルター条件を満たすメジャーの集計 (合計) 値です。
 セルB4と同様にセルB5の値もセルB6の値も、すべてのセル値は、それぞれが、それぞれのコンテキストフルタ―条件を満たすメジャーの集計 (合計) 値です。

・「用途」=「A住居専用住宅」を適用する
・「日付 (年)」=「2020」を無視する
・「建築主」=「国」は適用する

 各セルに適用されている列のコンテキストフィルター (「日付 (年)」) を無視し、列の「用途」と「日付 (年)」に従っててメジャーを合計すれば、すべてのセルは列の親集計の値になります。

「日付 (年)」が「2020」「2021」「2022」いずれであろうが、これらを無視するからすべてのセルの値が列の親集計になります。

=(CALCULATE(SUM('データテーブル'[建築物の数 (棟)]),
DATEADD(PREVIOUSYEAR('データテーブル'[日付]),0,year)
,ALLSELECTED('データテーブル'[日付 (年)]))
+CALCULATE(SUM('データテーブル'[建築物の数 (棟)]),
DATEADD(PREVIOUSYEAR('データテーブル'[日付]),-1,year)
,ALLSELECTED('データテーブル'[日付 (年)]))
+CALCULATE(SUM('データテーブル'[建築物の数 (棟)]),
DATEADD(PREVIOUSYEAR('データテーブル'[日付]),-2,year)
,ALLSELECTED('データテーブル'[日付 (年)]))
+SUM('データテーブル'[建築物の数 (棟)]))
/CALCULATE(SUM('データテーブル'[建築物の数 (棟)])
,ALLSELECTED('データテーブル'[日付 (年)]))

結果が計算の種類のときと一致していれば成功です。