【PowerBI】マトリックスで最大値・最小値をハイライトする方法

こんにちはMJです。

マトリックスでどのセルが一番「大きいか・小さいか」を探すとき、スライサーで期間を変えるたびに目で探し直していませんか。

今回はDAXで動的テーブルを作り、スライサーに応じて最大値と最小値のセルをハイライトする方法を紹介します。

完成イメージ

スライサーで期間を選ぶと、マトリックスの中で売上が最大のセルが緑、最小のセルが赤になります。

動的テーブルとは

DAXでは、「VAR」を使ってテーブルを変数に入れられます。このテーブルはスライサーなどのフィルターに反応して、その都度中身が作り直されるため「動的テーブル」と呼ばれます。
今回は以下の3ステップで作ります。

１．行と列の組み合わせのテーブルを作る（SUMMARIZE関数）

２．各組み合わせの売上を列として追加する（ADDCOLUMNS関数）

３．スライサーに反応するようにする（CALCULATETABLE関数 + ALLSELECTED関数）

サンプルデータ

約1万行の売上データを使います。使う列は「オーダー日」「都道府県」「サブカテゴリ」「売上」で、都道府県は45件、サブカテゴリは17件です。

マトリックスは、行に都道府県45件、列にサブカテゴリ17件を置きます。（組み合わせは45x17 最大765セル）

1. 基本メジャーを作成

まず、セルの値になる売上の合計メジャーを作ります。

売上Total = SUM('オーダー'[売上])

このメジャーは、この後の動的テーブルの中で使います。

2. 動的テーブルを作成

いきなり完成式を書くと読みにくいため、先ほどの3ステップに分けて作成します。

① 行と列の組み合わせのテーブルを作る（SUMMARIZE関数）

SUMMARIZE関数は実際にある行から、指定した列の組み合わせを重複なしで返します。

今回のサンプルデータでは売上データがない組み合わせは含まれず、全組み合わせ(45×17=765)より少ない681件になりました。

② 各組み合わせの売上を列として追加する（ADDCOLUMNS関数）

ADDCOLUMNS関数の式に売上合計メジャーを指定すると、都道府県とサブカテゴリの組み合わせごとに売上が計算されます。

③ スライサーに反応するようにする（CALCULATETABLE関数 + ALLSELECTED関数）

ALLSELECTED()関数は、セル自身の行・列のフィルターを外し、スライサーのフィルターだけをそのまま適用します。これにより、変数で作成した動的テーブルの中で最大値と最小値を比較できます。

※DAXクエリビューにはスライサーがないため全期間の合計

3. 最大・最小を判定するメジャーを作成

動的テーブルを変数Pに入れ、最大値・最小値を各セルの値と比較します。

最大最小判定 = 
VAR P =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE (
                'オーダー',
                'オーダー'[都道府県],
                'オーダー'[サブカテゴリ]
            ),
            "選択売上", [売上TOTAL]
        ),
        ALLSELECTED ()
    )
VAR minV = MINX ( P, [選択売上] )
VAR maxV = MAXX ( P, [選択売上] )
VAR curV = [売上TOTAL]
RETURN
    SWITCH ( TRUE (), curV = minV, 1, curV = maxV, 2 )

• minV、maxV：動的テーブルの「選択売上」列から最小値、最大値を取得
• curV：各セルの売上
• SWITCH ( TRUE (), curV = minV, 1, curV = maxV, 2 )：各セルの値を確認しながら最小なら「1」、最大なら「2」を返します。

4. マトリックスを作って背景色の条件式を設定

マトリックスビジュアルを作成し、フィールドを次のように設定します。

次に、ビジュアルの書式設定の「セル要素」で、系列「売上Total」の背景色から「fx」を開き、条件付き書式を設定します。

「最大最小判定」メジャーは色ではなく「1」、「2」を返すだけなので、色を変えたいときは規則の色を変えるだけで済みます。

結果

オーダー日のスライサーを動かすと、緑と赤のセルがその期間の最大・最小に自動で移ります。

まとめ

今回は、DAXの変数に動的テーブルを作り、スライサーに連動して最大値と最小値のセルをハイライトする方法を紹介しました。
動的テーブルは VAR、CALCULATETABLE、ALLSELECTED を組み合わせて作ります。ALLSELECTED でセル自身のフィルターを外すことで、スライサーで選んだ範囲の全セルを見渡して比較できます。

スライサーで条件を変えるたびに目で最大・最小を探していた方は、ぜひ試してみてください。