テクテク日記

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

Power BIで作るZチャート②

前回の記事では、Zチャートの基礎・用途・DAXによる構築等について紹介しましたが、今回は時点選択によるダイナミックなZチャートの作り方を紹介します(前回記事は以下より)。

ダイナミックZチャートとは

売上Zチャートはその時点から遡って12ヵ月、売上・累積売上・移動年次総計の3つの指標をラインチャートに表した形がZに似ていることに由来しています。Zチャートは常に最新時点から遡って作られ、会計年度ベース or 最新時点をベースとして常に直近12ヵ月のZチャートが作れる、ということを前回話しました。

今回紹介するテクニックは、選択時点(通常年月)ベースにZチャートを選択時点から遡って12ヵ月で表示できるやり方です。このやり方が特徴的なのは以の通り。

  1. データモデルを少し変更する必要がある
  2. Zチャートを構成するメジャーのDAX式を変更する必要がある
  3. Zチャート用のYear Monthスライサーは、他のビジュアルと切り離して設定
  4. 過去のZチャートを参照可能(Zチャートを好きな時点で切り替えられる)

このうち、1~3は設定が必要でやや面倒ですが、4はこれによって得られる恩恵となります。普通のやり方では過去に作ったZチャートを再現することはできませんが、ダイナミックZチャートは下図のように、各選択時点のZチャートを可視化できるため、非常に面白いインサイトが得られるかもしれません。

ダイナミックZチャートと通常のZチャートの違いは以下の通り。

  • ダイナミックZチャート
    年月という時点(Point of Time)で可視化
  • 通常のZチャート
    Year Flag(常に直近12ヵ月の日付テーブル)というフィルターで可視化

データモデルの構築

ダイナミックZチャートを作るためには、まずデータモデルを少し変更する必要があります。おさらいとして、通常のデータモデルは以下の通りでした。

ダイナミックZチャートのデータモデルは、以下のように変更します。

  1. Date_visualという日付テーブルを計算テーブルとして追加。既に作っているDateテーブルを複製するだけで簡単に作成可能

  2. DateテーブルとDate_visualテーブル同士に非アクティブな多対1のリレーションシップを構築(=Date_visualでDateをフィルターするリレーションシップ)

  3. 新しいデータモデルの出来上がり

これだけでデータモデル(リレーションシップの部分)は完了です。次にDAXを新たに作ります。

DAXの構築

DAXを作る前に、前提条件として前回記事で紹介した2つのDAX(SalesAmtとMAT Sales)が構築済である必要があります。その前提で、以下のDAX式を新たに作ります。なお、前回の売上と区別するため、ダイナミック○という呼び名にします。

ダイナミック売上高(Dynamic Sales)

Dynamic Sales = 
IF(
    [HasSales],  
    VAR _period_to_use = 12 //months to go back
    VAR _current_date_number = MAX ( 'Date'[Year Month Number] ) //current month number
    VAR _period =
        FILTER (
            ALL ( 'Date' ),
            'Date'[Year Month Number] <= _current_date_number
                && 'Date'[Year Month Number] > _current_date_number - _period_to_use
        )   //extract the range for the past 12 months
    VAR _result =
        CALCULATE (
            [SalesAmt],
            REMOVEFILTERS ( 'Date' ),
            KEEPFILTERS ( _period ),
            USERELATIONSHIP ( 'Date'[Date], Date_visual[Date] ) 
            //activate the inactive relationships to display sales of the past 12 months when selecting from Date
        )
    RETURN
        _result
)

恐らく一番面倒かつ難しいのが、このDynamic Salesというメジャーでしょう。無事メジャーが構築できれば、DateテーブルのYear Monthスライサーで、選んだ時点から遡って12ヵ月の売上が抽出できるようになります。

なお、留意点として、以下のように、スライサーはDateテーブル、テーブルビジュアルはDate_visualテーブルから選択する必要があります。

ラインチャートもこれと同じパターンにする必要があり、

日付(Start of Date)は常にDate_visualを使う

と覚えておくと良いでしょう。また、スライサーは下図のように、「単一選択」に設定しておきます。

ちなみに、DAX式の変数_period_to_useの12を6に変更すれば、選択時点から遡って6ヵ月の売上を抽出することができます。

この式の特徴は以下3つ

  1. 記述の通り、_period_to_useの数値を変更すれば、その数値から遡った月数の売上を抽出可能
  2.  'Date'[Year Month Number]を使用しており、この列は月のシークエンスを数字で表記したもの。例えば、2009年12月は24119、2010年1月は24120となっています。計算方法は以下の通り。
    Year Month Number = Year * 12 + Month - 1
    この数字自体に意味はなく、Time Intelligence関数が使えない場合に、カスタムで時系列フィルターを操作する必要があるときに、このようなテクニックで対応することになります
  3. _resultでDateフィルターをREMOVEFILTERSで外し、USERELATIONSHIP関数で非アクティブのリレーションシップをアクティブにさせ、_periodにて定義したフィルターを設定する

この辺りはDAXに慣れている人でも難しいですが、パターンの1つとして覚えておくと良いかもしれません。続けて、ダイナミック年次総計売上のDAXを記述していきます。

ダイナミック年次総計売上(Dynamic MAT Sales)

Dynamic MAT Sales =
IF (
    NOT ISBLANK ( [Dynamic Sales] ),
    CALCULATE (
        [MAT Sales],
        USERELATIONSHIP ( 'Date'[Date], Date_visual[Date] ),
        REMOVEFILTERS ( 'Date' )
    )
)
//MAT = Moving Annual Total

Dynamic MAT Salesを見ると、下図のように、選択時点から常に直近12ヵ月の結果が表示されています。原理はDynamic Salesとほぼ同じですが、計算式はだいぶ楽になっています。

最後に、ダイナミック累積売上のメジャーです。

ダイナミック累積売上(Dynamic YTD Sales)

Dynamic YTD Sales =
VAR _selected_month = MAX ( 'Date'[Start of Month] )
VAR _start_month =
    DATE ( YEAR ( _selected_month ), MONTH ( _selected_month ) - 12, 1 )
VAR _axis_month =
    SELECTEDVALUE ( Date_visual[Start of Month] )
VAR _period =
    FILTER (
        ALLSELECTED ( 'Date_visual' ),
        'Date_visual'[Start of Month] >= _start_month
            && 'Date_visual'[Start of Month] <= _axis_month
    )
VAR _result =
    CALCULATE ( [Dynamic Sales], _period )
RETURN
    IF ( _axis_month <= _selected_month, _result )
//YTD = Year-to-Date

このメジャーの作り方で結構苦労しましたが、私と同じチームのPhil Seamarkさんに相談したら、あっさり解決法を提供してくれました(Thank you so much!)。

DAX式は非常に簡単ですが、2つのDateテーブルの関係性を確実に理解しておかないと実現できないメジャーになります。ちなみに、PhilさんはPower BI CAT(Customer Advisory Team)のDAXエキスパートで、Power BIでゲームまで作ってしまうくらいです。凄い以外の言葉が出ない程、凄いと言わざるを得えません。

ということで、最終的に以下のようなダイナミックZチャートの出来上がりです。

繰り返しになりますが、X軸やテーブルのStart of MonthはDate_visualから持ってきているところに気を付ける必要があります。最終的に作られたこのダイナミックチャートですが、Year MonthスライサーがDateテーブルから来ているため、「単一選択」となっていることから、他のビジュアルにも影響してきます。そこで下図のように、

ダイナミックZチャート以外のビジュアルは相互左右=なし

に変更しておくと良いでしょう。

Year MonthスライサーをダイナミックZチャート専用のスライサーとして固定

しておくと使い勝手が良くなるはずです。

スライサーの相互作用の公式ドキュメントは以下より。

まとめ

2回にわたり、Zチャートのディープダイブを紹介してきました。ダイナミックZチャートはニーズがあるか分かりませんが、個人的に面白い可視化の1つであると思いましたので、紹介することにしました。これで皆さんもZ戦士(Z Fighters)の仲間入りです!ドラゴンボール世代をお許しください・・