PowerBIで累計を作成するには？
はじめに
初めまして、分析屋のCです。
昨年5月より業務効率化チームに所属しており、現在はデータ整備やPowerBIのレポート作成・保守などを担当しています。
技術ブログではPowerBIに関するTipsや検証、BIに関連する技術の勉強した内容のまとめなどを書いていこうと思っています。
さて、今回はDAXの累計についてまとめたいと思います。
例えば下図のように日次で販売利益を累計した線グラフを表示したい時など、「DAXで累計を出したい」という場面はかなり多いです。
DAXには多種多様な関数が搭載されているので、「累計」と一口に言っても色々なやり方があります。
そこで、今回はDAXでの累計の主な実装方法を2つご紹介したいと思います。
（おまけで1つだけPowerQueryでの累計の実装方法もご紹介しています）
使用するデータ
Excelで作成した購買データをPowerQueryで読み込んでいます。
日付・時刻順に並んでいますが、データはランダムで作成しているため、連続日付になっていません。
また、スライサー等の設定用にDAXでカレンダーマスタを作成し、日付テーブルとして設定しています。
テーブル間のリレーションシップは以下の通りです。
レポートの構成はこんな感じです。
各種スライサーと結果確認用の折れ線グラフ、データ詳細用のテーブルを配置しています。
折れ線グラフはX軸が日付、Y軸が累計、凡例が商品カテゴリになっています。
DAXでの累計の実装方法
1.TOTALYTD関数/TOTALQTD関数/TOTALMTD関数
最もオーソドックスな方法だと思います。
TOTALYTD関数
構文：TOTALYTD(<評価する式>,<日付>[,<フィルター>][,<年度末の日付>])
DAX式とグラフは以下の通りです。
オプションの<年度末の日付>を設定していないので、12/31で累計がリセットされています。
DAX式の年度末の日付を3/31に設定すれば、年度間の累計も計算できます。
TOTALYTD関数は年ごとに値がリセットされますが、TOTALQTDは四半期ごと、TOTALMTDは月ごとに値がリセットされます。
年度末の日付の指定オプションがなくなるだけで、使い方はほぼTOTALYTDと一緒です。
2.CALCULATE関数
TOTALYTD関数/TOTALQTD関数/TOTALMTD関数では、必ず年度末/四半期末/月末に値がリセットされてしまいます。
そのため、例えば年を跨いで累計を算出したい場合は別の関数を使う必要があります。
そんな時に使えるのがCALCULATE関数です。
CALCULATE関数
構文：CALCULATE(<評価する式>[,<フィルター1>,<フィルター2>...])
DAX式とグラフは以下の通りです。
CALCULATE関数ではフィルターが複数設定可能なため、例えば「総額が5000円以上の購買履歴のみ合計したい」といった場合には、下記のように条件を追加するだけで実装できます。
フィルター部分は色々バリエーションがあり、
WINDOW関数やDATESINPERIOD関数、DATEADD関数、ALL/ALLEXCEPT/ALLSELECTED関数などがよく使われている印象です。
特にWINDOW関数は日付軸でなくても使えるので、かなり便利です。
CALCULATE関数を自在に使えるようになるとPowerBIでの表現の幅がぐっと広がるので、私も日々勉強中です。
ぜひ皆さんも色々調べてみてください。
おまけ：PowerQueryで累計を実装する方法
PowerQueryで累計を実装することは経験上かなりレアです。
「スライサーで指定した範囲内での累計を見たい」という場合がほとんどのため、フィルターの値に応じて再計算してくれるDAXで実装してしまうことが多いからです。
PowerQueryで累計を作成した場合、スライサーで集計期間やカテゴリを絞ることができません。
（2025年1月はじまりだと23Mから始まっている画像）
とはいえ、「データ量が多すぎてDAXの再計算に時間がかかる」等、PowerQuery側で累計を作成するシチュエーションもあり得ます。
という訳で、あまり例はありませんが、PowerQueryでの累計実装方法をご紹介します。
1.List.FirstN
データの上から順に連番を振り、List.FirstN関数でデータの先頭～当日までの合計を算出する方法です。
まず、日付単位で総額を集計したいため、日付でグループ化します。
次に、行インデックスを作成します。
インデックスは「列の追加」→「インデックス列」で作成できます。
カスタム列を追加し、下記のように入力します。
List.FirstN関数で先頭行～インデックスの番号の行までの「総額」列の値リストを取得し、それをList.Sum関数で合計しています。
グラフにするとこんな感じです。
まとめ
DAXは多機能で便利ですが、その分やりたいことの実現方法が複数あり、どの方法を選択するかは状況によってかなり変わってきます。
また、月次のアップデートでも関数が追加されており、できることもどんどん拡張されていきます。
例えば、2024年2月のアップデートで追加されたRUNNINGSUM関数では、ビジュアル上で累計や移動平均の計算が可能だとか…。
いつかその辺りのプレビュー機能なども記事にしたいですね。
では、ここまで読んでいただきありがとうございました。
この記事が皆さんの参考になれば幸いです。
ここまでお読みいただき、ありがとうございました！
この記事が少しでも参考になりましたら「スキ」を押していただけると幸いです！
分析屋ではBIを使ったデータ可視化に関するサービスサイトもございます。Power BIに関するお困りごとがございましたらお気軽にお問い合わせください。
株式会社分析屋について
弊社が作成を行いました分析レポートを、鎌倉市観光協会様HPに掲載いただきました。
ホームページはこちら。
noteでの会社紹介記事はこちら。
【データ分析で日本を豊かに】
分析屋はシステム分野・ライフサイエンス分野・マーケティング分野の知見を生かし、多種多様な分野の企業様のデータ分析のご支援をさせていただいております。 「あなたの問題解決をする」をモットーに、お客様の抱える課題にあわせた解析・分析手法を用いて、問題解決へのお手伝いをいたします！
【マーケティング】
マーケティング戦略上の目的に向けて、各種のデータ統合及び加工ならびにPDCAサイクル運用全般を支援や高度なデータ分析技術により複雑な課題解決に向けての分析サービスを提供いたします。
【システム】
アプリケーション開発やデータベース構築、WEBサイト構築、運用保守業務などお客様の問題やご要望に沿ってご支援いたします。
【ライフサイエンス】
機械学習や各種アルゴリズムなどの解析アルゴリズム開発サービスを提供いたします。過去には医療系のバイタルデータを扱った解析が主でしたが、今後はそれらで培った経験・技術を工業など他の分野の企業様の問題解決にも役立てていく方針です。
【SES】
SESサービスも行っております。