見出し画像

PowerBI 任意の期間での比較機能を実装しよう（後編）

Tak_17854

はじめに

前回の記事で、ビジュアルに必要なスライサーの作成まで完了しました。
今回は追加したスライサーテーブルを使用して、ビジュアル設定を行っていきます。

前回記事はこちら

要件のおさらい

  1. 異なる２つの年月（yyyy/mm）を選択するために、スライサーを二つ配置する。（基準月と比較月とする）

  2. 配置する２つのスライサーはどちらも単一の月を選択し、異なる２期間の売上比較を行う。

  3. 追加要件として、比較月では基準月よりも未来の年月を選択できない、かつ基準月では比較月よりも過去の年月を選択できないようにする。

  4. 基準月/比較月どちらのスライサーから選択しても３の要件を満たすようにする。

追加テーブルのおさらい

スライサー用 = 

/*基準月用*/
var Base_Table = 
    CALCULATETABLE(
        DISTINCT(
            SELECTCOLUMNS(
                'Time',
                "基準月",
                FORMAT([Month],"yyyy/mm")
            )
        ),
        'Time'[ReportingPeriodID] in VALUES(Sales[ReportingPeriodID])
    )

/*比較月用*/
var Comp_Table =
    DISTINCT(
        SELECTCOLUMNS(
            Sales,
            "比較月",
            FORMAT([yyyymmdd],"yyyy/mm")
        )
    )

/*直積を作る*/
var CJ_Table = 
    CROSSJOIN(Base_Table,Comp_Table)

return
    ADDCOLUMNS(
        CJ_Table,
        "FLG",
        IF([基準月] > [比較月], TRUE(), FALSE())
    )

スライサーを設定する

レポートビューでスライサーを２つ追加し、それぞれ基準月と比較月を設定します。
要件２を満たすため、スライサーを単一選択にします。　
また、要件３と４を満たすためにビジュアルに細工を入れます。

追加テーブルのDax式のreturn以降を確認するとわかる通り、ADDCOLUMNS関数でFLGという列を追加しており、基準月 > 比較月の場合にTrueが入ることがわかります。

このFLG列をスライサーのビジュアルフィルターとして適用します。

比較月についても同様にビジュアルフィルターを設定してください。

動作検証

基準月で2014/07を選択すると、比較月では2014/06以前しか選択できません。
比較月で2014/02を選択すると、基準月では2014/03以降しか選択できません。

上記のように要件を満たしたスライサーを設定することができました。

ファクトテーブルからの値の引っ張り方

以前の記事でも書きましたが、どこにもリレーションを設定しないほうが都合がいいテーブルも存在します。そして、今回の追加テーブルもどこにもリレーションをつなぐ必要がないテーブルです。

しかしながらリレーションをつないでいないため、このままではスライサーの項目をいくら選択しようがファクトテーブルの集計結果に影響を与えることはできません。

ただDax式をうまく記述してやれば、リレーションがつながっていなくても集計結果に影響させることができます。

基準月 vs 比較月 の作り方

対象表記 = 
var Base = SELECTEDVALUE('スライサー用'[基準月])

var Comp = SELECTEDVALUE('スライサー用'[比較月])

return
    Base & " vs " & Comp

基準月と比較月はどちらもスライサーが単一選択に設定されているため、SELECTEDVALUE関数で単一の値を取得できます。

基準月の売上 - 比較月の売上の作り方

売上比較 = 
var Base = SELECTEDVALUE('スライサー用'[基準月])

var Base_Sales = 
    CALCULATE(
        SUM(Sales[Sum_Regular_Sales_Dollars]),
        FORMAT(Sales[yyyymmdd],"yyyy/mm") = Base
    )

var Comp = SELECTEDVALUE('スライサー用'[比較月])

var Comp_Sales = 
    CALCULATE(
        SUM(Sales[Sum_Regular_Sales_Dollars]),
        FORMAT(Sales[yyyymmdd],"yyyy/mm") = Comp
    )

var Sales_Value = Base_Sales - Comp_Sales

return
    FORMAT(Sales_Value,"+#,##0;-#,##0")

今回売上の集計対象列はSum_Regular_Sales_Dollars列としました。
CALCULATE関数を使用することで、Salesテーブルのうち基準月と比較月のそれぞれの年月と合致するものを集計し、FORMAT関数で表示を整えています。

成長率（基準月の売上/比較月の売上）

成長率 = 
var Base = SELECTEDVALUE('スライサー用'[基準月])

var Base_Sales = 
    CALCULATE(
        SUM(Sales[Sum_Regular_Sales_Dollars]),
        FORMAT(Sales[yyyymmdd],"yyyy/mm") = Base
    )

var Comp = SELECTEDVALUE('スライサー用'[比較月])

var Comp_Sales = 
    CALCULATE(
        SUM(Sales[Sum_Regular_Sales_Dollars]),
        FORMAT(Sales[yyyymmdd],"yyyy/mm") = Comp
    )

var Sales_Value = DIVIDE(Base_Sales,Comp_Sales)

return
    FORMAT(Sales_Value,"#.0%")

色の設定にこだわる場合

プラス成長なら青色のフォントカラー、マイナスなら赤色のフォントカラーみたいなことをやりたければ、下記Dax式を作成

Font条件 = 
var Base = SELECTEDVALUE('スライサー用'[基準月])

var Base_Sales = 
    CALCULATE(
        SUM(Sales[Sum_Regular_Sales_Dollars]),
        FORMAT(Sales[yyyymmdd],"yyyy/mm") = Base
    )

var Comp = SELECTEDVALUE('スライサー用'[比較月])

var Comp_Sales = 
    CALCULATE(
        SUM(Sales[Sum_Regular_Sales_Dollars]),
        FORMAT(Sales[yyyymmdd],"yyyy/mm") = Comp
    )

Return
    SIGN(Base_Sales-Comp_Sales)
対象ビジュアルの条件付き書式でDax式を判定させる。

ビジュアル完成

作成できたビジュアルはこんな感じ

確認

Salesテーブルの集計結果

2014/07の売上は4,746,142.74、2014/05の売上は4,861,220.71

(2014/07の売上) - (2014/05の売上) = -115,077.97
(2014/07の売上) ÷ (2014/05の売上) = 0.976327

問題なさそうですね！
ちなみに売上の月次トレンドはこんな感じでした。

おわりに

任意の２点間の比較にリレーション外のテーブルを利用して、Dax式を使うことで実現する方法を紹介しました。

今回のような要件の場合に、リレーション内のテーブルの項目をスライサーに使用すると、ALL関数等を使用してフィルタを外して集計させる方法を考える必要が出てきます。

紹介した方法は、そのようなフィルタリングを意識することなく実装できる点と、比較と基準がどちらも同じ形式でDax式を書けばいいため、混乱が生じにくい点がメリットかなと思います。

参考になれば幸いです。
最後までお読みいただき、ありがとうございました。

いいなと思ったら応援しよう！