[Power BI]スナップショットABC分析を実装する① ～DAX Studioを使ってバーチャルテーブルを検証編～

6168 ワード
DAX Qiitadelika PowerPlatform delika PowerBI PowerBI テキストリンク

はじめに

ABC分類については、別記事にて静的ABC分類をまとめています。

続編も公開しました！

元ネタはみんな大好き、DAX Patternsです。

スナップショットABC分類とは

スナップショットとは、

ある瞬間の状態を抜き出したもの

・・という意味合いです。

以前取り組んだ「静的」ABC分類では、売上テーブル（Sales）のすべての売上を総計してABC分類を行いました。

実際の業務では、年度ごとや、4半期ごとなど、日付で区切り場合も多いと思います。

例えば年度ごとのスナップショットを作成し、製品（Products）を年度ごとにABC分類します

スナップショット ABC分類を実装する

テーブル作成

スナップショットABC分類は下記のコードを使用して新しいテーブルを作成します。

DAX式

ABC by Year = 
var produstbyyear = 
SUMMARIZE(
    'Sales',
    'Products'[商品ID],
    '日付テーブル'[年_yyyy年]
    )
var SalesByYearProduct = 
ADDCOLUMNS(
    produstbyyear,
    "@ProdSales",[受注額],
    "@YearlySales",CALCULATE(
        [受注額],
        ALL('Products')
    )
)
var CumulatedSalesByYearProduct=
ADDCOLUMNS(
    SalesByYearProduct,
    "@CumulatedSales",
    var CurrentSales = [@ProdSales]
    var CurrentYear = '日付テーブル'[年_yyyy年]
    var CumulatedSalesWithinYear = 
        FILTER(
            SalesByYearProduct,
            AND(
                '日付テーブル'[年_yyyy年] = CurrentYear,
                [@ProdSales] >= CurrentSales
            )
        )
        return
        SUMX(
            CumulatedSalesWithinYear,
            [@ProdSales]
        )
)
VAR CumulatedPctByYearProduct =
    ADDCOLUMNS (
        CumulatedSalesByYearProduct,
        "@CumulatedPct", DIVIDE (
            [@CumulatedSales],
            [@YearlySales]
        )
    )
VAR ClassByYearProduct =
    ADDCOLUMNS (
        CumulatedPctByYearProduct,
        "@AbcClass", SWITCH (
            TRUE,
            [@CumulatedPct] <= 0.7, "A",
            [@CumulatedPct] <= 0.9, "B",
            "C"
        )
    )
VAR Result =
    SELECTCOLUMNS (
        ClassByYearProduct,
        "ProductKey", 'Products'[商品ID],
        "Calendar Year", '日付テーブル'[年_yyyy年],
        "ABC Class", [@AbcClass]
    )
return
Result

順番に解説

上記DAXは非常に長く、読み解くためにDAXStudioを使用して変数をひとつずつ紐解いていきます。

VAR produstbyyear

SUMMARISE関数を使用してSalesテーブルを商品IDと年でサマライズします。
商品IDごとに売上年が表示されているのがわかります。

VAR SalesByYearProduct

次は今作成したバーチャルテーブルに、ADDCOLUMNS関数を使用して列を追加します。
事前に作成しておいた[受注額]メジャーをそのまま使うと、年と製品のフィルターがかかった数字が追加され、
製品（Products）のフィルターコンテキストをALL関数ですべて選択するようにすると、結果的に年だけのフィルターがかかった数字が追加されます。

VAR CumulatedSalesByYearProduct

この変数では年ごとの累計値の追加をしています。その行の売上よりも高い売上を合算する（[@ProdSales] >= CurrentSales）処理をして累計値列が作成されています。

VAR CumulatedPctByYearProduct

ここまでくるとあとは簡単になってきます。ADDCOLUMNS関数とDIVIDE関数を使用して、累計売上をその年の総売上で割った数字の列を追加します。

VAR ClassByYearProduct

あとはABCの各分類を割り振るだけとなります。ここではしきい値を70％と90％に設定して、ABCを割り振ります。IF関数を使用して書いても良いのですが、こういう場合はSWITCH関数を使用するとスマートです。

VAR Result

最後の仕上げです。SELECTCOLUMNS関数を使用して最終的なテーブルを作成します。

  • ProductKey
  • Calender Year
  • ABC Class

ABC分類が完成

ひとまずこれでABC分類が完成しました。次回は今回作成したテーブルを元に、年度・クラスごとに売上をマトリクスビューで表示させるために追加の処理をしていきます。