Power BI で上位Nとその他を動的に表示する方法
目次
- はじめに
- 事前準備
- 1. 計算テーブルの作成
- 2. Rank メジャーの作成
- 3. 上位 N 選択肢の作成
- 4. 上位 N とその他の売上をグループ化
- 5. 並び替えの設定
- 6. グラフに条件付き書式を設定
- おわりに
はじめに
Power BI では「上位 N」フィルターを使用して、上位/下位何件を表示させるか設定することはできるのですが、残ったデータを「その他」にまとめて表示することはできません。
本記事では、DAX を使用してそれをに可能にする方法についてご紹介します。 (下図は完成イメージ)
事前準備
今回使用するのは商品名と売上金額からなる売上データです。(テーブル名:SalesTable)
以下の DAX 式を使用して、売上合計を集計するメジャーを作成しておきます。
TotalSales = SUM('SalesTable'[Sales])
1. 計算テーブルの作成
まず、Product 列に「その他」のデータを追加する必要があります。
以下の DAX 式を使用して、すべての商品を含む新しいテーブル ProductTable を作成し、最後に「Others」の値を持つ行を追加します。
ProductTable = UNION(ALL('SalesTable'[Product]), ROW("Product", "Others"))
次に、作成した計算テーブル ProductTable と元のテーブル SalesTable 間でリレーションシップを作成します。
ここで Product と TotalSales をテーブルで表示してみると、「Others」の TotalSales が表示できないことが分かります。
2. Rank メジャーの作成
TotalSales の大きい順で商品にランク付けを行うメジャーを作成します。
Rank = RANKX(ALLSELECTED(ProductTable[Product]), [TotalSales])
3. 上位 N 選択肢の作成
新しく計算テーブルを作成し、上位 N 件の選択肢を用意します。
TopNSelection = {1,2,3,4,5}
上位 N をレポート上で指定できるようにしたいので、
スライサーを追加して TopNSelection の Value をドラッグします。
スライサーで選択した値を後述の計算に反映するため、以下の DAX 式でメジャーを作成します。
SelectedN = SELECTEDVALUE('TopNSelection'[Value])
4. 上位 N とその他の売上をグループ化
Rank > SelectedN の商品を Others にまとめて売上合計を表示するメジャーを作成します。
TotalSalesWithOthers = SWITCH(TRUE(), // 1. Product が Others の場合:N+1 以降の Product の売上合計を表示 SELECTEDVALUE('ProductTable'[Product]) = "Others", CALCULATE( [TotalSales], FILTER( ALLSELECTED('ProductTable'[Product]), [Rank] > [SelectedN] ) ), // 2. Rank <= SelectedN の場合:各 Product の TotalSales を表示 [Rank] <= [SelectedN], [TotalSales], // 3. それ以外の場合:空白を表示 BLANK() )
上位 N+1 以下の商品を Others にまとめて売上合計を表示できるようになりました。
5. 並び替えの設定
TotalSales 降順で並び替えると下図のようになります。
Others を最後に表示したいときは、以下のメジャーを追加する必要があります。
RankWithOthers = SWITCH(TRUE(), // 1. Product が Others の場合:SelectedN+1 SELECTEDVALUE('ProductTable'[Product]) = "Others", [SelectedN] + 1, // 2. Rank <= SelectedN の場合:Rank [Rank] <= [SelectedN], [Rank], // 3. それ以外の場合:BLANK BLANK() )
テーブルに RankWithOthers をドラッグして RankWithOthers の昇順で並び替えると、Others が最後に表示されます。
6. グラフに条件付き書式を設定
棒グラフで表示してみるとこのようになります。
視覚的に分かりやすくするため上位 N とその他で棒グラフの色を分ける場合は、条件付き書式の設定を行います。
まずは以下の DAX 式を使用してメジャーを作成します。
Color = SWITCH ( SELECTEDVALUE ('ProductTable'[Product]), "Others", "#BBB", -- Others の場合はグレー "#118DFF" -- それ以外は青 )
メジャーの作成ができたら、グラフに条件付き書式を設定します。
設定を適用するとこのようになります。
おわりに
以上、上位 N とその他を動的に表示する方法のご紹介でした。
他にも Power BI で見つけた便利な機能や Tips を記事に残していきたいと思います。