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

# はじめに

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

https://qiita.com/hanaseleb/items/b80e28459096e40c3808

続編も公開しました！

https://qiita.com/hanaseleb/items/49fe30e107b0936ad156

元ネタはみんな大好き、[DAX Patterns](https://www.daxpatterns.com/)です。

https://www.daxpatterns.com/

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

スナップショットとは、

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

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

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

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

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

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

### テーブル作成

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/8d9d4ae5-cba1-c57c-e77d-8eebfa091499.png)

### 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ごとに売上年が表示されているのがわかります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/0b600760-5d6d-cb9a-a195-2544000a09c2.png)

https://docs.microsoft.com/ja-jp/dax/summarize-function-dax

### VAR SalesByYearProduct

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/8498c14a-e90d-1f2d-6dce-6b8cdacfe384.png)

https://docs.microsoft.com/ja-jp/dax/addcolumns-function-dax

### VAR CumulatedSalesByYearProduct

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/c8cafeef-37c7-3b56-ff56-a3aa7bf3ccf5.png)

### VAR CumulatedPctByYearProduct

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/e680f59f-4631-0557-f912-1c099a77b0b8.png)

https://docs.microsoft.com/ja-jp/dax/divide-function-dax

### VAR ClassByYearProduct

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/020cc935-de60-455d-472a-f12bf5bba234.png)

https://docs.microsoft.com/ja-jp/dax/switch-function-dax

### VAR Result

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

- ProductKey
- Calender Year
- ABC Class

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/cceed810-f5d5-a372-b474-6bf09a9fed7a.png)

https://docs.microsoft.com/ja-jp/dax/selectcolumns-function-dax

## ABC分類が完成

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/b9a46f2a-44bd-9e68-5fed-3899ba365f8e.png)