【簡単】PowerBIで累積計算できるメジャーの作成(テンプレコードあり)

PowerBI DAX 累積計算 累積合計DAX関数
スポンサーリンク

PowerBIでダッシュボードの作成時に、売上などの累積計算をしたい場面がありますよね。今回はDAX関数で累積計算のメジャーを作成する方法を紹介します。

【本記事で得られること】
・累積合計のメジャーを作成
・年や年度でリセットする累積計算(TOTALYTD関数)
・汎用性の高い累積計算(テンプレコードあり)

今回紹介する累積計算の方法は汎用性が高く、使い勝手が良いです。年や年度での累積合計の計算などは、使う場面も非常に多いので、ぜひマスターしてみてください!

DAX関数で累積計算のメジャーを作成

使い勝手の良い、2種類の累積計算のメジャー作成方法を紹介します。

◆TOTALYTD関数で累積計算

【DAX】
=TOTALYTD(式, Dates, [フィルター], [YearEndDate])
・式:SUMなどで売上合計などの式を指定
・Dates:日付テーブルの日付を指定

・フィルター:適用するフィルター、省略可
・YearEndDate:リセット日付、規定は12/31、省略可

TOTALYTD関数は年でリセットされる累積計算ができる関数!

▶(参照)Microsoft DAX関数 TOTALYTD

例えば、式に売上合計、Datesに日付テーブルを指定することで、売上合計の累積計算のメジャーを作成できます。

売上累計YTD_年 = TOTALYTD(SUM('売上データ'[売上]),'日付テーブル'[Date])

◆汎用性の高いCALCULATE関数やFILTER関数を用いた累積計算
詳しくは後述しますが、下記コードでも同様の結果が得られます。一見複雑ですが、やっていることはシンプルで、こちらのほうが汎用性が高いです。

売上累計(年) = 
CALCULATE(SUM('売上データ'[売上]),
    FILTER(ALL('日付テーブル'),
    '日付テーブル'[Date] <= MAX('日付テーブル'[Date]) &&
    '日付テーブル'[年_数値] = MAX('日付テーブル'[年_数値])))

売上棒グラフと累積合計の折れ線グラフ
累積計算をマスターすると、下記のように年や四半期ごとの売上合計の累積グラフを作成できます!年でリセット、四半期でリセットされる累積合計を折れ線グラフで追加しています。

PowerBI DAX 累積計算 累積合計 四半期 年

もちろん、リセットされない単純な累積計算のメジャーを作成することも可能です。

PowerBI DAX 累積合計 累積計算

・TOTALYTD関数を用いた簡単な方法
・DAX関数を組み合わせた汎用性の高い方法

の2種類の累積計算の方法について、売上データを用いて詳しく説明していきます!

スポンサーリンク

TOTALYTD関数で累積計算

まずは、TOTALYTD関数を使った累積計算できるメジャーの作成方法を説明します。

◆使用するデータ
今回PowerBIに取り込むデータは下記の製品、性別、地域別の売上データです。

PowerBI 取り込みデータ

▶(参考記事)PowerBIへのデータの取り込み
こちらのデータを使って実際に売上合計の累積計算メジャーを作成していきます。

◆リレーション
PowerBI内にカレンダーテーブルを作成し、取り込んだ売上データと日付列でリレーションさせています。

PowerBI リレーション

カレンダーテーブルの簡単な作成方法、リレーション方法は下記記事をご参考ください。
▶(参考記事)PowerBIで一瞬でカレンダーテーブルを作成する方法
▶(参考記事)PowerBIのリレーションでデータの連携

◆累積合計のメジャーの追加
売上合計の累積計算をしたいので、TOTALYTD関数でメジャーを作成していきます。

【DAX】
=TOTALYTD(式, Dates, [フィルター], [YearEndDate])
※フィルター、YearEndDateは省略可能

TOTALYTD関数は年でリセットされる累積計算ができる関数!

年でリセットされる累積計算

TOTALYTD関数はデフォルトで年でリセットされる累積計算ができるので、
式:SUM(売上列)
Dates:カレンダーテーブルの日付列

年周期で売上の累積合計ができるメジャーを作成できます。

売上累計 DAX TOTALYTD関数
売上累計YTD_年 = TOTALYTD(SUM('売上データ'[売上]),'日付テーブル'[Date])

作成したメジャーをダッシュボードのマトリックスで確認します。
行:年、月(カレンダーテーブル)
値:売上合計、作成したメジャー「年度累計YTD_年」

売上合計と売上累計(年)のマトリックス表

TOTALYTD関数 DAX 累積計算

年でリセットされる売上の累積合計ができていることが分かります!
▶(参考記事)PowerBIでテーブルの作成方法、条件付き書式の設定
▶(参考記事)PowerBIでマトリックスの作成

売上棒グラフと累積合計の折れ線グラフ
次に「折れ線グラフおよび集合縦棒グラフ」でTOTALYTD関数で作成した累積合計をグラフ化していきます。
X軸:年、四半期、月
列のY軸:売上の合計
線のY軸:作成したメジャー「年度累計YTD_年」

累積合計 TOTALYTD PowerBI DAX 折れ線グラフおよび集合縦棒グラフ 

月次の売上の棒グラフと、年でリセットされる売上の累積合計の折れ線グラフを表示できました!

【結論】
TOTALYTD関数を使えば簡単に年でリセットされる累積計算を作成できる!

スポンサーリンク

年度でリセットされる累積計算

続いて、年ではなく”年度”でリセットされる累積計算を作成します。

先ほど作成したメジャーに「”3/31”」を追加すれば、3/31でリセットされる累積計算にすることが可能です。

売上累計 DAX TOTALYTD関数 年度
売上累計YTD_年度 = TOTALYTD(SUM('売上データ'[売上]),'日付テーブル'[Date],"3/31")

こちらもマトリックスに追加して確認します。

売上合計と売上累計(年)、売上累計(年度)のマトリックス表

TOTALYTD関数 DAX 累積計算 年度

3/31を指定したので、年度でリセットされる累積合計ができました!

【結論】
TOTALYTD関数はリセットされる日付を指定でき、年度の累積計算なども可能!

スポンサーリンク

四半期や月でリセットされる累積計算

TOTALYTD関数は年周期での累積計算しかできませんが、
・TOTALMTD関数
・TOTALQTD関数
ですと、M:Month(月) Q:Quarter(四半期)の周期でリセットした累積計算ができます。
▶(参照)Microsoft DAX関数 TOTALMTD
▶(参照)Microsoft DAX関数 TOTALQTD

例えば、TOTALQTD関数で先ほどと同様にメジャーを作成します。

売上累計YTD_四半期 = TOTALQTD(SUM('売上データ'[売上]),'日付テーブル'[Date])

●四半期Verの売上と累積グラフ
こちらの四半期Verのメジャーを折れ線グラフのY軸に追加すれば、四半期でリセットされる売上の累積合計を表示することができました!

四半期 TOTALQTD関数 TOTALMTD TOTALYTD 累積合計 DAX

【結論】
TOTALYTD関数、TOTALMTD関数、TOTALQTD関数、を使えば年、月、四半期周期でリセットする累積計算ができる!

しかし、年を超えた全体の累積計算をしたい場合は今回の方法ではできません。TOTALYTD関数は簡単に使える一方で応用がきかない課題があります。そこで次に、汎用性が高いCALCULATE関数とFILTER関数を組み合わせた累積計算の方法を紹介します!

スポンサーリンク

汎用性の高い累積計算(テンプレコードあり)

CALCULATE関数やFILTER関数を用いた、汎用性の高い累積計算の方法を紹介します。こちらのやり方ですと、年や年度でリセットされる累積のほか、リセットされない全データの累積計算などご自身の欲しい形でメジャーを作成可能です。

CALCULATE関数、FILTER関数、ALL関数をそれぞれ理解しておけば簡単です。下記でそれぞれ詳しく説明しているので、ご参考ください!
▶(参考記事)DAX関数 CALCULATE関数の使い方、事例紹介
▶(参考記事)DAX関数 FILTER関数の使い方、事例紹介
▶(参考記事)DAX関数 ALL関数の使い方、事例紹介

全データの累積計算(リセットしない)

リセットさせずにすべての累積合計をするメジャーは、下記になります。

DAX関数 累積計算 売上累計 CALCULATE関数 FILTER関数 ALL関数 PowerBI

下記はテンプレコードなので、コピーしてご自身のPowerBIで新しいメジャーを作成し活用ください。

売上累計 = 
CALCULATE(SUM('売上データ'[売上]),        //売上合計
    FILTER(ALL('日付テーブル'),           //全ての日付を対象にする
        '日付テーブル'[Date] <= MAX('日付テーブル'[Date])))   //その最終日の累積計算

【解説】
①CALCULATE(SUM(売上列),
→CALCULATE関数は条件を追加した計算ができるので、SUMで売上合計を指示
②FILTER(ALL(日付列)
→FILTER関数の中にALL関数で全ての日付を対象にする。
③日付列 <= MAX(日付列)
→各月の最終日にあたる日付のMAX以下を計算するように指示。

これで、年でリセットされない売上累計のメジャーを作成できました!

PowerBI DAX 累積合計 累積計算 マトリックス
PowerBI DAX 累積合計 累積計算 折れ線グラフ

【結論】
CALCULATE関数やFILTER関数、ALL関数を組み合わせて、リセットされない累積計算のメジャーを作成できる!

スポンサーリンク

年、年度でリセットした累積計算

年、年度でリセットした累積計算を作成する場合は、先ほどのメジャーに1行追加します。

売上累計 年 累積計算 CALCULATE関数 FILTER関数 ALL関数
売上累計(年) = 
CALCULATE(SUM('売上データ'[売上]),
    FILTER(ALL('日付テーブル'),
    '日付テーブル'[Date] <= MAX('日付テーブル'[Date]) &&
    '日付テーブル'[年_数値] = MAX('日付テーブル'[年_数値])))

【解説】
①CALCULATE(SUM(売上列),
②FILTER(ALL(日付列)
③日付列 <= MAX(日付列)
→①②③の部分は先ほどと同じです。
日付テーブル'[年_数値] = MAX(‘日付テーブル'[年_数値])
④を「&&」で追加することで、年でリセットした累積計算ができます。その年のMAX、つまり最終日までは累積計算するという指示を追加しています。

年度でリセットさせたい場合は、カレンダーテーブルの年度列を指定すればOKです。

ただこれは、リレーションさせたカレンダーテーブルに年や年度の列があることが前提です。下記記事のカレンダーテーブルのテンプレコードから作成した場合は、自動生成されるので問題ありません!
▶(参考記事)PowerBIで一瞬でカレンダーテーブルを作成する方法

テンプレコードの④の列を任意に変えれば、累積計算したい範囲を自由に変更できます!

PowerBI 累積計算 四半期 年

【結論】
CALCULATE関数やFILTER関数を組み合わせれば汎用性の高い累積計算メジャーが作成できる!

スポンサーリンク

PowerBIで累積計算の方法まとめ

今回説明したDAX関数で累積計算のメジャーを追加するポイントは下記です。

・TOTALYTD関数で簡単に年別の累積計算ができる
・CALCULATE関数、FILTER関数、ALL関数を組み合わせた累積計算の方法をマスターすれば汎用性が高い!

今回同様にPowerBIの便利機能とDAX関数をまとめていますので、ぜひこちらもご参考ください!
▶PowerBIの便利機能、powerQueryまとめ

よく使うDAX関数まとめ

最後までみていただき、ありがとうございました!
良かったら↓いいねクリック応援をよろしくお願いします!!

にほんブログ村 サラリーマン日記ブログ IT系サラリーマンへ
にほんブログ村

スポンサーリンク

コメント

タイトルとURLをコピーしました