小計で四半期末の数字だけを集計する

「集計は四半期ごとだけど、期中の推移も見たい」的なお話です。
この手の要望に対応した事があるので、忘れないうちに書いておきます。

ExcelのPower Pivotでも使えます。
#やりたいこと
実績のデータがあります。

四半期の小計が3ヶ月の合計になってますが、これを期末(3月、6月、9月、12月)の数値にしたい。
つまりこういう事。

完成イメージはこちら。

ついでに四半期ごとに設定された計画地との比較もやる。

#使うデータと設定
##リレーション

##データセット
在庫実績

在庫計画

在庫実績テーブルは日付がyyyyMM形式の「年月」列で構成されています。
また、在庫計画は四半期ごとの粒度です。
このままでは日付テーブルとリレーションを組めないので、年月ブリッジと四半期ブリッジを中間テーブルとしてブリッジさせます。

年月ブリッジ

四半期ブリッジ

##日付テーブル

日付テーブル

日付テーブル = 
ADDCOLUMNS(
    CALENDAR(DATE(2018,4,1),DATE(2021,3,31)),
    "年_数値", YEAR ( [Date] ),
    "年_yyyy年",FORMAT([Date],"yyyy年"),
    "月_数値", MONTH ([Date]),
    "月_MM月", FORMAT ([Date], "MM月" ),
    "日_数値", DAY ([Date]),
    "日_DD日", FORMAT([Date],"DD日"),
    "年月_数値", VALUE(FORMAT([Date], "yyyyMM" )),
    "年月_yy年MM月", FORMAT ( [Date], "yy年MM月" ),
    "年度_数値",if(MONTH([Date])>3,YEAR([Date]),YEAR([Date])-1),
    "年度_0年度",FORMAT(if(MONTH([Date])>3,YEAR([Date]),YEAR([Date])-1),"0年度"),
    "四半期_数値", if(MONTH([Date])<4,4,if(MONTH([Date])<7,1,if(MONTH([Date])<10,2,3))),
    "四半期_0Q", if(MONTH([Date])<4,"4Q",if(MONTH([Date])<7,"1Q",if(MONTH([Date])<10,"2Q","3Q"))),
    "年度四半期", if(MONTH([Date])<4,YEAR([Date])&"4Q",if(MONTH([Date])<7,YEAR([Date])&"1Q",if(MONTH([Date])<10,YEAR([Date])&"2Q",YEAR([Date])&"3Q"))),
    "年度四半期末", SWITCH(MONTH([Date]),3,TRUE(),6,TRUE(),9,TRUE(),12,TRUE(),FALSE())
    )

##メジャー
事前に定義したメジャーはこちら。

メジャー

在庫実績合計 = SUM('在庫実績'[在庫実績])
在庫計画合計 = SUM('在庫計画'[在庫計画])

#小計で四半期末の数字だけを集計する
##どういうデータにすれば出来そうか
期末の実績だけ抜き出せれば何とかなりそうです。

##メジャーの準備
###期末の数字を取り出す
実は難しいことをしなくてもできます。
日付テーブルを作った際に、期末の月がTrue、それ以外がFalseになる計算列年度四半期末を作ってあります。

年度四半期末

"年度四半期末", SWITCH(MONTH([Date]),3,TRUE(),6,TRUE(),9,TRUE(),12,TRUE(),FALSE())

後はCALCULATEにフィルター式として組み込むだけです。

期末実績

期末実績 = CALCULATE([在庫実績合計],'日付テーブル'[年度四半期末]=TRUE())

これで期末の実績が取り出せました。

###四半期小計と総計以外を識別する
ISINSCOPE 関数を使います。

ColumnNameに指定する列は'日付テーブル'[月_MM月]です。

この式を使う事で月ごとの集計ならTrue、それ以外はFalseを返します。

月集計チェック

月集計チェック = ISINSCOPE('日付テーブル'[月_MM月])

期中の計算でもしっかり働いてくれます。

ここら辺の手順は**Power BI の神がメジャーの特性 や 見出しの階層に合わせて集計する には**で詳しく解説してくれています。

###四半期小計と総計を期末の数字にする
既にISINSCOPE('日付テーブル'[月_MM月])でTrueになるのが月ごとの集計と分かっています。
なので**Trueなら在庫実績合計を、Falseなら[期末実績]を表示するようにします。**

ここではSWITCH 関数を使います。

小計と総計が期末の数字

在庫実績合計 = SUM('在庫実績'[在庫実績])
期末実績 = CALCULATE([在庫実績合計],'日付テーブル'[年度四半期末]=TRUE())

小計と総計が期末の数字 = SWITCH(TRUE(),ISINSCOPE('日付テーブル'[月_MM月]),[在庫実績合計],[期末実績])

これで目的の表が出来ました。

#四半期ごとに設定された計画値と比較する
次は四半期ごとに設定された計画値との比較を作ります。

実はこのままでも四半期ごとの比較はできます。

けど、折角やるなら全期間を比較したいですよね。

##空白の月を埋める
全期間で比較するためには空白の月を計画値で埋める必要があります。
まず**CALCULATEでフィルターコンテキストを上書きします。**

CALCULATEでフィルターコンテキストを上書き

CALCULATEでフィルターコンテキストを上書き = CALCULATE([在庫計画合計],ALL('日付テーブル'))

フィルター式にALL('日付テーブル')を使う事で、空白の部分を全て埋められました。

しかし全て総計値で埋まってしまってるので、これを各四半期の計画値に変える必要があります。
そこでフィルター式にVALUES('日付テーブル'[年度四半期])を加えます。

こうすることで**ALL('日付テーブル')で日付テーブルのフィルターを全て外した上で、年度四半期ごとのフィルターだけが適用された状態にフィルターコンテキストを上書きできます。**

式はこうなります。

実績比較用在庫計画

実績比較用在庫計画 = CALCULATE([在庫計画合計],ALL('日付テーブル'),VALUES('日付テーブル'[年度四半期]))

これで正しく各四半期の計画値で空白を埋める事が出来ました。

##計画と実績を比較する
後は計画と実績を引き算するだけです。

作成した計画 = CALCULATE([在庫計画合計],ALL('日付テーブル'),VALUES('日付テーブル'[年度四半期]))
作成した実績 = SWITCH(TRUE(),ISINSCOPE('日付テーブル'[月_MM月]),[在庫実績合計],[期末実績])

計画-実績 = [作成した計画]-[作成した実績]

全期間の比較が完成しました。

#まとめ

• メジャーだけではなく、計算列も使う
  • 計算列でテーブルに固定値を持った方が便利な場合も沢山あります
• テーブルやマトリックスの行、列から生まれるフィルターコンテキストを条件式に利用する
  • DAX式はフィルターの有無であったり、フィルターの結果1つの値が残るかを取得できる関数があります
• 個別の値と小計、総計を別々の式で表示する事が出来る
  • DAX式は自分で小計、総計だけ別の計算式にする事が出来るので、学べば学ぶほど表現が広がります。

DAX式はExcelと大分作法が違いますが、Excel関数だけでは難しい集計が出来ますよー。