2022年10月のPower BI Desktopのリリースで紹介された機能の1つに、Reverse stack order for stacked column charts(積み上げチャートのスタック順番を逆にする)という機能が登場しました。
この機能は凡例の並び順と積み上げチャートの順番を揃えることでチャート全体の違和感を解消するものですが、今回紹介するのはこれとは違った形で並び替えをコントロールするものとなります。なお、このやり方はスライサーの使い勝手も改善できるやり方であり、ユーザービリティという観点でも重宝できると思います。
※ いつも通り、pbixファイルだけ欲しい方は一番下よりどうぞ
基本事例
積み上げチャートに限らないですが、Power BIでは列で並び替え機能が良く使われます。よくあるパターンが時系列データを1月~12月でプロットした場合、1月からスタートするのではなく、10月からスタートするパターンです。
下図は年と月、そして売上高をテーブルビジュアルでそのまま表示した際の結果ですが、月が1月からではなく10月から並び替えされた状態になっています。Power BIを使い始めた頃に結構ショックだった動作で、余程のニーズがない限り、このような並び方が良いという人は少ないでしょう。
1月から並び変えるためには、2つ方法があり、1つは日付テーブルの月という列を01月、02月....12月といった書式に変更することです。これにより、列で並び替えを行う必要はありませんが、0が9月まで前に付くので好き嫌いは分かれるでしょう。
もう一つは最もメジャーで今回のトピックですが、列で並び替えを使用することです。前提として、月(列名: Month Name)の並べ替え用の列(Month Number)があることが必須となります。また、Month Nameは合計12個のユニークな文字で構成されているため、Month Numberも12個のユニークな数字である必要があります。
解説に入る前に、前提条件を確認しておきます。
- ソースデータ
- Contoso Retail DWのサンプルデータセット
- テーブル解説
- fSales: 売上データ
- DimProduct: 商品マスタ
- dCalendar: Bravo for Power BIで作った日付マスタ(テーブル)
- リレーションシップ
- データモデル
ここから列で並び替えを行うには以下のステップの順に行います。
ポイントは1月から並べ替えしたい列を最初に選択(ステップ④)し、列で並び替えからペアとなる数字列(Month Number)を選択(ステップ⑥)することです。これを行うことで、結果は以下のようになります。
応用事例
積み上げチャートの並び替え
時系列ベースのデータを並べ替えを行うのに、上記の例で事足りるでしょう。今回紹介する事例は積み上げチャートを作った場合、
各スタック(積み上げ)の順番を金額の大きい順から下から積み上げていく
というやり方になります。結論から見ていきますと、下記チャート(上のほう)をその下にあるチャートのように分類別の順番を変更していくことです。
アルファベットをベースとした積み上げチャート
売上高をベースとした積み上げチャート
積み上げチャートに関しては基本的に金額が大きい分類を一番下に持ってくるのが見やすいです。また、分析という観点から金額が大きい分類の変動が最も重要であることもあり、上図左側のテーブルと一緒に表示した際に分類の並び順が統一されていることがしっくりくると思います。
並び替え用の列(CategorySort)は以下のように作ります。
RANKXを使って、CategoryKeyに対して、売上が最も大きいものを1、最も小さいものを8という順番で計算していきます。ALLEXCEPTはCategoryKeyのフィルターだけを尊重し、それ以外のフィルターを全てALLで解除しており、これによりCategoryName別の売上高(SalesAmt)がCALCULATEによって算出され、それを基にRANKXが分類別ランキングを計算していくロジックとなります。
試しにCALCULATEの中身だけを計算列として追加した場合、以下のようになります。
※ CALCULATE + ALLEXCEPTの書き方はExcel Power Pivotではどうやら機能しない模様
このSalesByCategoryで算出された売上数字は3年分の分類別売上であり、これは下図から確認できます。
ちなみに、通常RANKXを使用してメジャーとしてランキングを算出する場合、最初のパラメータであるテーブル(ここではDimProduct)もALLでフィルター解除をしてあげる必要がありますが、計算列はRow Contextの概念(対象テーブルを1行ずつスキャンしていくもので、メジャーではSUMX、FILTER等のDAX関数がこれに相当)となるため、ALLはあってもなくても良い(※ ここでは説明用にあえてALLを使用していませんが、明示的にALL( DimProduct )にしたほうがよいでしょう)。
ここまで来ればあとはCategoryNameをCategorySortで並べ替えるだけです。
チャートに戻ると、以下のようにHome Appliancesという分類が一番下に来ていることが分かります。
スライサーの動作
この売上をベースとした並び替えはスライサーを使う時でも重宝できます。通常のスライサーと並び替え後のスライサーを比べると以下のようになります。
aのスライサーは列で並び替えを行っていないパターンで、アルファベット順に並んでいることが分かります。一方、bを見ると下のテーブルにある分類名と同じ並び順となっており、aであれば見当もつかない売上ランキングがbでは一発で分かるようになっているわけです。
分析業務が必要な場合、スライサーを使って売上規模の大きい分類をいち早く特定できることが重要であり、今回のやり方が非常に便利であると言えます。
問題提起
RANKXによる列で並び替えは非常に便利であることが分かりました。しかし、全てがパーフェクトというのは難しく、このやり方の細かい部分について理解しておく必要があります。
留意点① 売上が重複する分類
今回の想定は
分類別の売上に重複が重複が存在しない
ものとなります。
データ量が十分にあり、分類名も多くない場合、分類別に売上が重複するようなことは稀であるため、今回紹介したやり方で殆ど対応できますが、留意点は売上がタイ(同じ)になっていた場合です。売上がゼロの分類が存在する場合もこれに当てはまりますが、そのような場合では分類を思い通りに既存の列で並び替えで行うことができなくなってしまいます。
以前はエラーが発生し、並び替えができなかったはずだが、最新のPower BI Desktopで試すとエラーを再現できなかったので、もしかするとエラー回避仕様になったのかもしれません。
留意点② 多種多様な並べ替え
今回の例では並べ替え用の算出数字(1~○)は3年分の売上をベースとしていますが、例えば特定時点の値(例:直近月の売上 or 直近3ヵ月の売上)で並び替えを行うこともできます。
例えば、直近1ヵ月の売上で並べ替えを行う場合、以下のようになります。
DAX式はシンプルでCALCULATEの中にLASTDATEというTime Intelligence関数を入れるだけで直近月の売上だけを取得し、その中でランキングを行ってくれます。これにより、上図の通り、今までHome Appliancesが一番下に来ていたものの、TV and Videoという分類が一番下に来るようになりました。
※LASTDATEはStart of Monthという列に対してフィルターを行っており、上図は月次ベースの推移となっている。日次推移では異なる結果が返ってくる可能性があることに留意されたい
同様に直近3ヵ月の売上をベースに並び替えをしてみると、以下のようになります。
直近3ヵ月で見るとやはりHome Appliancesが一番稼いでいるようです。ちなみに、もう少し分かりやすくするため、[直近3ヵ月売上]というメジャーを作ってみたところ、以下のように全ての分類名で直近3ヵ月の売上になってしまいました。
理由はALLEXCEPTがCategoryKey以外の列のフィルターを解除しているためであり、ここはCategoryNameにするか、ALLEXCEPT自体を削除すると正しい結果が返ってきます(下図)。
さて、なぜこのような考え方になったかというと、例えば直近で売れ筋となっている分類を一番下に持っていきたい、といったニーズが考えられるからです。また、このロジックを応用すれば、年間売上やある時点の在庫残高を使って積み上げスタックの順番をコントロール(例:在庫と売上のぞれぞれの分類別積み上げチャートをどちらかの順番に合わせてチャートを作ってレポートに表現する、等)することもできるようになりますので、ニーズ次第で様々なパターンを作り出すことができると思います。
留意点③ CategoryKeyがない場合の動作
最後に、CategoryKeyがなかった場合の動作について紹介したいと思います。商品マスタの場合、殆どのケースで商品コード(ProductKey)をベースにリレーションシップを構築することになると思います。商品コードと商品名、中分類コードと中分類名、大分類コードと大分類名というように、本来はコードとそれに紐づく名称があることが普通です。
しかし、リレーションシップが最も細かい粒度である商品コードで構築された場合、商品コードの上の階層であるこれら分類別コードを省略したくなる気持ちが出てくることがあります(理由はいろいろですが、単純にデータサイズの削減、列数を極限に減らしたい等)。私も昔はそれをやっていましたが、今回のように積み上げチャートで並び替えが必要な場合は少し考える必要があります。
結論から言いますと、CategoryKey列ではなくCategoryName列に対して列で並び替えを行うと、
循環参照が発生
し、並べ替えがうまくいかなくなります。すなわち、以下のように、ALLEXCEPTの中でDimProduct[ProductName]をパラメータとして渡してしまうと、循環参照エラーが発生するわけです。
循環発生パターン
分類名別の売上に対してランキングを計算することが循環参照になってしまうようです。これに対する解決法は2つありますが、どちらを採用されるかは好み次第です。
- CALCULATEの部分を外だしにして、SalesByCategoryという計算列を追加(①)し、更に一列追加してRANKXを使ってSortByCategory(例ではSortByCategory2)を算出(②)し、列で並び替えを行う(③)
このやり方は分かりやすいですが、列が2つ追加されてしまうため、モデルサイズの肥大化や、データモデル更新時のパフォーマンスに影響する可能性が出てくるかもしれません - もう一つのやり方はもっとシンプルで、上記「循環発生パターン」の②まで実施した後、CategoryName列をCatNameという列で複製し、この新しい列を使って列で並び替えを行うとうまくいきます(下記①~④)
2番目の方法のメリットはお手軽というのがポイントですが、- 列を新たに追加する必要がある
- データの重複が発生(新規追加したCatName列)
- 当初のCategoryNameを使用することができない(=新しいCatName列を使用する必要がある
といったちょっとしたマイナスポイントも存在します。なお、複数の切り口が存在する混乱を避けるため、このやり方ではCategoryName列を非表示にしておくとユーザーエクスペリエンスが向上します。
最後に
以上、列で並び替えに関して細かい部分まで見てきましたが、個人的には積み上げチャートの順番はもとより、自分で並び替えの順序ロジックを組んでコントロールできること、そして忘れてはならないのが、スライサーの一番上に重要な分類(金額が大きい分類)を持ってこれる部分が一番使い勝手が良い部分だと感じています。