テクテク日記

テクテク=テクノロジー&一歩ずつ(テクテク)

Power BIで作るZチャート①

Power BIを使っていくと、Zチャートというビジュアルを作ることがあります。Zチャートは様々な業界で使われており、指標の概要は以下の通り(デモ用のデータは一番下に貼っておきます)。

  • 特徴
    月次値、累積値、移動年次総計の2つの指標によって構成。正しく作れるとZのようなチャート。

  • 使い所
    Zの傾きがトレンドを表しており、売上ベースの場合、右肩上がりは売上増トレンド、右肩下がりは売上減トレンド、平らな状態は売上横ばいトレンド


    上図は売上が右肩上がりのトレンドで推移しているものの、直近では急降下しているシグナルを出している状態です。月次売上(下図)を見ると、直近1ヵ月の売上が前年から急落しているのが原因となっていることが分かります。

  • メリット
    • 直感的でわかりやすい
    • 3つの指標を同時に表示可能
    • ドラゴンボールZが好きな人はZチャートも好き(という噂)
  • デメリット
    • 会計年度ベースで見る場合、データがまだ発生していない月では累積値が途中で切れてしまう
    • DATESYTDを使用すると、第2のパラメータを手動で設定しないといけない
    • 観測時点が変わるとZチャートではなくなってしまう

Zチャートの作り方自体、Power BIのTime Intelligence関数を知っていれば難しいものではないですが、上記デメリットがボトルネックとなってZチャートを使うのを躊躇してしまうこともあると思いま。そこで、今回はちょっとマニアックなことも含め、2回に分けて、事例別に解決策について紹介したいと思います。

Zチャートを使ったビジュアル

Zチャートは何も売上だけ考える必要はなく、在庫管理を行っている場合、出庫数量・入庫数量等で可視化を行うことも可能です。ただ、一般的に売上ベースで考えたほうが分かりやすいので、これ以降は全て売上ベースで話をしていきます。

Zチャートは時系列別に、月次売上・累積売上・移動年次総計の3つを同時に、直近1年でプロットした場合の形がZに似ていることから命名されています。売上前年比を行うことができないため、前年比といった比較結果を可視化することに向いていませんが、これをカバーするため、(上記説明済ですが)他のビジュアルをレポートに入れることで対処できます。同時に3つの指標を出してトレンドを把握するという意味においては、Zチャートは使い勝手が良いと思います。

Zチャートを構成する指標

以下、それぞれのメジャーについて、作っていきます。前提として、データモデルは以下のように、日付テーブル(Date: Dimension)と売上テーブル(Sales: Fact)でリレーションシップが構築されているものとします。

DateテーブルはBravoを使えば簡単に作ることができ、詳細は以下の記事をご参考ください。

Bravoを使って、Dateテーブル(BravoでTime Intelligence関数を作っていないという前提)とリレーションシップが構築できたら、下記メジャーを記述します。なお、今回はStart of Dateを使いますので、追加方法は上記ブログからご確認ください。

売上有無フラグ(HasSales)

HasSales =
VAR _LastDateWithData =
    CALCULATE ( MAX ( Sales[DateKey] ), REMOVEFILTERS ( Sales ) )
VAR _FirstDateVisible =
    MIN ( 'Date'[Date] )
VAR _Result = _FirstDateVisible <= _LastDateWithData
RETURN
    _Result
//日付テーブルが年末(○年12月31日)まで伸びているため、YTD Salesが年末まで伸びて出現するのを防ぐフラグ

売上高(Sales)

SalesAmt = SUM ( Sales[SalesAmount] )

累積売上(YTD Sales)

YTD Sales = 
IF(
    [HasSales],
    CALCULATE( [SalesAmt], DATESYTD( 'Date'[Date] ) )
)
//12月31日をベースとした年間累積売上
//YTD = Year-to-Date

年次総計売上(MAT Sales)

MAT Sales =
IF (
    [HasSales],
    VAR _current =
        CALCULATE ( MAX ( Sales[DateKey] ), ALLEXCEPT ( Sales, 'Date' ) )
    VAR _period =
        DATESINPERIOD ( 'Date'[Date], _current, -1, YEAR )
    VAR _result =
        CALCULATE ( [SalesAmt], _period )
    RETURN
        _result
)
//MAT = Moving Annual Total

最後の年次総計売上については下記ブログで細かく解説しています。DATESINPERIOD関数はなかなかやっかいな関数で、以前の記事ですが、下記ご参考ください。

上記4つのメジャーを作り、SalesAmt とYTD Sales、MAT Salesをラインチャートに入れればZ戦士。。。ではなく、Zチャートの出来上がりです。

殆どの場合、これでニーズが満たされると思いますが、デメリットの解説を含む事例を見ていきます。

事例1: 会計年度ベースのZチャート

上図の通り、これが最もオーソドックスなZチャートかもしれませんが、直近1年をそのまま表現したものとなります。常に12ヶ月のデータが揃っていることが前提であり、データがない場合、累積売上が途中で切れてしまうことになります(下図)。

その為、会計年度(12ヵ月ベース)でしかZチャートを見ることが出来ず、他のチャート(例: 年次総計売上のみ)で代用した方が売上トレンドを把握するという意味では現実的かもしれません。

事例2: 自動更新Zチャート

事例1を少し変化させたのが、

データ更新→常に直近12ヶ月のZチャート

となります。例えば、2009年1月から2009年12月までのZチャートを作り、2010年1月のデータが追加されてPower BIが更新されると自動的に1ヶ月分だけスライドした状態のZチャートというパターンです。

ポイントはデータ更新と一緒にZチャートも更新されるところであり、実はこれが結構難しい。

先程の累積売上のメジャーはDATESYTDを使用しており、第2の引数はYearEndDateを入れるのですが、2010年1月までの12ヶ月累積売上を作ろうとすると、以下のようにDAX式を変更する必要があります。

累積売上(YTD Sales)

YTD Sales = 
IF(
    [HasSales],
    CALCULATE( [SalesAmt], DATESYTD( 'Date'[Date], "1/31" ) )
)
//1月31日をベースとした年間累積売上
//YTD = Year-to-Date

この第2の引数(YearEndDate)はパラメータ化させることは出来ず、必ず"1/31""2/28"といったように、テキスト形式、かつマニュアルで入力する必要があります。YearEndDateを入力せず、データモデルのデータを更新すると、Zチャートは以下のように変な形となってしまいます。

レポートの自動更新を構築している人にとっては最高に歯痒いことでしょうが、マニュアルで"1/31"と入力すると正しいZチャートとなります。集計期間を1ヵ月スライドさせるだけですが、これがややこしい(下図は正しいZチャートが表示されていますが、実際には年スライサーだけでは実現できません)。

また、もう一つ注意しないといけないのが、常に直近12ヶ月のデータを使ってZチャートを作るということは、常にこの期間のデータでZチャートをフィルターしていることであり、このフィルターが無ければ以下のチャートのように、意味不明なチャートが出来上がってしまうのです。

こちら、何かの芸術?と見間違えるほど、素晴らしい勾配を描いていますが、Zチャートが対象年月で絞られていないがゆえに、3年+1ヵ月という範囲で可視化を許してしまった結果となります。

YTD Salesは2010年1月まで更新された状態ですが、ここで2009年で絞ってみると、2009年2月スタートとなったため、2009年1月が前年の年間累積売上となってしまいました。

2010年を追加してあげれば良いですが、これだと13ヵ月になってしまい、余計混乱してしまいます。

では、年月のスライサーを追加してZチャートをコントロールしてみましょう。これであれば問題ないでしょうが、このやり方は異様に面倒で、絶対にやりたくない&ユーザーからクレームの嵐が来ることがほぼ確実なレポートデザインです。

ではどうすれば、データ更新→Zチャートも更新ということを実現できるのでしょうか?答えは簡単、

常に直近1年を抽出できるスライサーを作る

ことです。下図の通り、「<=1yr」というのが、データが更新された場合、常にデータがある最新の月から12ヵ月遡った範囲までの日付テーブルを取得できるスライサーであり、その下の「Year」が2009(2月~12月)と2010(1月)という範囲になっています。

やり方は以下の通り。

  1. Dateテーブルにて、直近月から何ヵ月経過したかという列(MonthElapsed)を追加
  2. MonthElapsedをベースに列「YearFlag」を追加
    Power QueryでもDAXでもOKですが、DateテーブルがBravoで生成されているため、ここではDAXで追加します
  3. スライサーにした際に一番上に「<=1yr」が来るよう、「YearFlagSort」を追加
  4. 「YearFlag」を「YearFlagSort」で並べ替え(列の並べ替え)
  5. 累積売上(YTD Sales)のメジャーを変更

1~3まで作った計算列は下図のようになります。

ステップ1: 経過月の算出

データビューのテーブルを選択し、右クリック > 新しい列をクリック。

経過月数(MonthsElapsed)

MonthsElapsed =
VAR _most_recent_month =
    CALCULATE (
        MAX ( 'Date'[Start of Month] ),
        'Date'[DateWithTransactions] = TRUE (),
        REMOVEFILTERS ()
    )
VAR _month_passed =
    DATEDIFF ( 'Date'[Start of Month], _most_recent_month, MONTH ) + 1
RETURN
    _month_passed

2010年1月というSalesデータが含まれている場合、Dateテーブル(DAX生成)は必ず2010年12月末まで伸びているため、データが入っている直近月を見つけるためには、

 'Date'[DateWithTransactions] = TRUE ()

というフィルター条件を入れてあげる必要があります。この列はBravoで生成されたDateテーブルに含まれていますので、こちらで改めてDAXで書く必要がなく、非常に助かります。

これにより、変数_most_recent_monthが求められますので、あとはDATEDIFF関数を使ってStart of Month(各月の最初の日)と比較すれば、経過月数を求めることができます。

ステップ2: YearFlagの追加

ステップ1と同じく、下記計算列を作ります。

直近12ヵ月フラグ(YearFlag)

YearFlag = 
VAR _month_passed = 'Date'[MonthsElapsed]
VAR _result =
    SWITCH (
        TRUE (),
        _month_passed <= 0, "FutureDate",
        _month_passed <= 12, "<=1yr",
        _month_passed <= 24, "<=2yr",
        _month_passed <= 36, "<=3yr",
        _month_passed <= 48, "<=4yr"
        
    )
RETURN
    _result

何年分のデータを保持するかによって、このFlagの範囲が増減することに注意が必要です。なお、直近月は2010年1月になりますが、2010年2月のレコードも含まれますので、その場合_month_passedが0以下の数字になってしまいます。これは売上データがない将来日付に相当するため、”FutureDate”という名前にしています。

ステップ3: YearFlagSortの追加

ステップ2に習い、下記計算列を作ります。

直近12ヵ月フラグの並び替え用列(YearFlagSort)

YearFlagSort = 
VAR _month_passed = 'Date'[MonthsElapsed]
VAR _result =
    SWITCH (
        TRUE (),
        _month_passed <= 0, 5,
        _month_passed <= 12, 1,
        _month_passed <= 24, 2,
        _month_passed <= 36, 3,
        _month_passed <= 48, 4
        
    )
RETURN
    _result

「<=1yr」等のテキストを数字に変更したものです。FutureDateはスライサーの一番下に表示させたいため、5という数字を割り当てています。

ステップ4: 列の並べ替え

以下のように、YearFlagをクリックし、YearFlagSortで並べ替えを行います。

ステップ5: DAXの修正

最後のステップはYTD SalesのDAX式を修正します。

累積売上_修正(YTD Sales_revised)

YTD Sales_revised =
IF (
    [HasSales],
    CALCULATE (
        [SalesAmt],
        'Date'[Date] <= MAX ( 'Date'[Date] ),
        VALUES ( 'Date'[YearFlag] )
    )
)

この式のポイントは

という3つでしょうか。DAXについてある程度知っている人であれば、これが1つのパターンであることを知っていると思いますが、そうでない人はなかなかやり方を思いつかないのではないでしょうか。

VALUESにより、<=1yr、<=2yr...でフィルターコンテキストが効くようになり、売上が各12ヵ月の期間でリセットされた状態で累積されるようになりました。

最終的にYearFlagというスライサーで直近12ヵ月のZチャートを作ることになりますが、これでたとえ次に2010年2月のデータが入ってきた場合でも、<=1yrは自動的に2009年3月~2010年2月の範囲を更新してくれます。

ちなみに、通常のYTD Salesだと"1/31"で設定を行っているため、ビジュアルは以下のようになってしまいます。ここで改めて、DATESYTDの第2引数を"2/28"に変更する必要が出てきますので、やはりYTD Sales_revisedのほうが使い勝手が良いと言えます。

パート②について

今回はZチャートの特定ケースについて、データが更新されたらZチャートも自動更新されるパターンについて紹介しました。このやり方はシンプルで結構便利だと思いますので、ぜひとも活用して頂ければと思います。今回、例には出していませんが、異なる切り口(商品分類、店舗、地域等)でZチャートを使えば、更に細かい粒度で売上動向を把握することができるようになります。

パート①で満足する人が殆どだと思いますが、実はまだ下記ボトルネックについて回答できていません。

  • 観測時点が変わるとZチャートではなくなってしまう

そう、今はYearFlagで絞った場合に見ていますので、観測時点が常に最新の月となっています。これを例えば過去にさかのぼって、2009年10月、2009年6月というふうに、観測時点を自分でコントロールしてその時点のZチャートを簡単に見れるようになると便利だと思いませんか?

この場合、Zチャートの3つの指標を全てDAXで書き換える必要がありますが、データ更新が必要なくなり、より柔軟性の高いZチャートを構築できるようになります(下図)。

ということで、次回はこちらについて解説していきますので、ご期待ください。

marshal115.hatenablog.com

今回使用したpbixファイル
ダウンロード