[Excel BI] ピボット テーブルとメジャー

先日、Power BI 勉強会で「使える！クイック メジャー」というお題でお時間をいただき、メジャーの紹介をいたしました。勉強会にご参加いただいたみなさま、ありがとうございました。また、このブログで「Excel ユーザーにとってのメジャーとは？」というタイトルの記事を過去に投稿しています。参照数などから、この話題は興味のある人が多いようで、エクセル ユーザーも「メジャー」や Power BI といったキーワードが気になっているようですね。

Office 製品は、機能はもう十分です、追加は要りません、と言われた時期もありましたが、現在、Excel はちょっとした改善ではなく、ガラリと使い勝手が変わる機能強化が押し寄せてきているように感じます。

そのような機能強化の中で「これは・・・」と私も驚いた機能をご紹介します。先日の勉強会では、メジャーを理解するにはいったんExcelを(セルを基本とするワークシートを）忘れたほうがいいかもしれない、という紹介をしたのですが、実は、Excel のピボット テーブルで「メジャー」を使う、というお話です。

データ モデル利用のデメリット

VLOOKUP関数などを使わず、いわゆる「表引き」を実現する「リレーションシップ」の機能は、Excel 2013から標準機能として搭載されました。

[ブログ] テーブルのすすめ ピボット テーブルとリレーションシップ
https://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html

[Office Support] Excel のテーブル間にリレーションシップを作成する
https://support.office.com/ja-jp/article/fe1b6be7-1d85-4add-a629-8a3848820be3

このリレーションシップ機能は、分析するデータを「データ モデル」に格納して、ピボット テーブルを作成しますが、当初、データ モデルにより使えなくなる機能がいくつかありました。その代表が「日付のグループ化ができない」と「集計フィールドが使えない」でした。

データ モデル利用によりグレイアウトされた集計フィールド

日付のグループ化などは機能拡張で後日対応しましたが、集計フィールドは日付のグループ化のような後日対応はありませんでした。集計フィールド同等の機能は、Power Pivot のメジャーや計算列で対応が可能だったからだと思います。

しかし、Power Pivot はすべてのエディションで利用可能ではありません。一部古いバージョン用を除いて、ダウンロードセンターから Power Pivot アドインのダウンロードもできなくなりました。
例えば、Office 365 Business の Excel で Power Pivot を利用することはできません。Office 365 ProPlus と Excel 2016 の Professional Plus のエディションに限られ、このエディションにはアドインとして最初から同梱されてます。つまり、所有している Excel のアドインに同梱されていなければ利用できない、ということです。

Excel 2016 のデータ モデルのピボット テーブル

では、Power Pivot を利用できないエディションでメジャーの作成ができないのか、ということです。
Excel でメジャーの作成を検索すると Power Pivot を使う情報がほとんどですが、Excel 2016 以降、Power Pivot アドインを含んでいない Office 365 Business 含め、いずれのエディションでも、ピボット テーブルでデータ モデルを利用すると、Power Pivot なしでも「メジャー」の作成が可能になっています。

以下のようなシンプルな列で構成されるテーブルを使って検証してみます。

このような表やテーブルからピボット テーブルを作成する時、[ピボットテーブルの作成] ダイアログ ウィンドウの [このデータをデータ モデルに追加する] のチェックを入れて、データ モデルにテーブルを追加します。ピボットテーブルは「データ モデル」に追加され、そこから作成されます。

複数テーブルの分析をしなくても、このチェックをオンにしてデータ モデルからピボットテーブルを作ることができます。
ピポット テーブル作成後、フィールド リスト ウィンドウに表示されるテーブル（サンプルでは [Table01]）上で右クリックをすると、[メジャーの追加] を含むコンテキストメニューが表示されます。

[メジャーの追加] をクリックすると、メジャー のダイアログ ウィンドウが表示されます。ここで DAX を使ってメジャーの数式を作成・編集することが可能です。

集計フィールドの代替でメジャーを利用

Excel の集計フィールドの説明や解説では、テーブルの列の数値から消費税を計算したり、売上から原価を引いて粗利を計算したり、といった例が紹介されます。
データ モデルを使ったピボット テーブル レポートでは、集計フィールドのかわりにメジャーを使うことができますが、数式に相当する表記方法はメジャーの表記方法（＝DAX数式）を使う必要があります。

たとえば、数値A列の数値に 0.08 を乗算する消費税の計算のようなものだと、集計フィールドを使うと以下のような数式になります。

集計フィールドによる消費税計算

メジャーでそのまま同じ式を使うと以下のようになります。

これは本物のメジャーなんですね。ですから、ワークシート上の構造化参照式のような数式はエラーになるんですね。='table01'[数値A]*0.08 という数式を入れた時のエラーは以下です。

この数式は無効または不完全です: 'Calculation error in measure 'table01'[mj2]: A single value for column '数値A' in table 'table01' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.'。

'table01' の数値Aは単一の値として定義されていない（列で複数の行を持っている）から計算できません。min や max、count や sum などの集計関数を使って、単一の結果を取得して、それを使いなさい、というような内容です。

ピボット テーブル レポートの基本的な「動き」は、レポートの行ラベルまたは列ラベルとして設定された要素でグループ化をして集計するものです。よくあるのは行は製品区分、列は年度、のようなマトリクス表です。また、集計対象の要素が数値データで構成されていれば「合計」を計算し、数値以外を含んでいるデータであれば「個数」を集計します。これがいわゆる「暗黙のメジャー」というわけです。

ピボット テーブル レポートで計算が合わない原因

暗黙といわれるせいか、あまり意識されていないかもしれませんが、ピボット テーブルにおける数値列の計算は、まずは合計を計算します。たとえそれが列に対するパーセンテージを計算するものであっても、行ラベルのグループで「合計」をしてから、列合計を分母として、行ラベル合計を分子にして割合を計算、という順序です。（それゆえ実務では、サンプルとして使っている消費税の計算は、メジャーではなく、元のテーブルに列を追加して各行にて端数処理を行い合計すべきか、合計してから消費税を計算し、端数処理を行うかは注意しなければなりません）

ちょっと話がそれますが、ピボット テーブル機能が今ひとつ一般的にならず、人気がない、もしくは難しいといって避ける人が多いのは、個人的に上述の「まず、行・列のラベルのグループで合計しているんだよ」という暗黙の計算を意識していないせいではないかと思います。

ピボットあるあるの話で、かつ、間違った解説をしている記事がネット上にいくつかあるのですが、集計フィールドの解説で「受注金額は、単価*数量 で求められるので、ピボット テーブル レポートで 単価*数量 の数式の集計フィールドを追加すればいいんですよ。」と紹介しているものがあるのですが、これは完全な誤情報です。それ、本当に間違いです。Google での検索で結構上位にくるものでも、このような説明をしている記事を見ることができます。本当に注意してください。以下その解説です。

単純に以下を考えてみましょう。佐藤さんは、5月に、単価 100円のものを1個、単価1000円のものを1個売りました。佐藤さんの5月の売上はいくらでしょう？

100円*1個で100円と、1000円*1個で1000円、合計は 1,100円ですよね。

これをピボット テーブルの集計フィールドで、受注は 単価*数量 だからといって、受注の集計フィールドを作って計算すると、2,200円になります。2件しかありませんからすぐに間違いに気が付きますが、もし、件数が多い場合などすぐには気が付かないでしょう。

この計算は、まず、佐藤さんというレコード（行）を集めます。その次に、単価の列で合計します。単価は 100 と1000 が１つづつあるので 1100 です。次に数量の列で合計をします。1が2つあるので 2 になります。最後に集計フィールドで指定されている数式による演算を行います。1100 * 2 なので結果は 2200 です。

つまり、集計フィールドの例としてあげている数式の、受注金額＝単価＊数量 は、

受注金額 = SUM(テーブル[単価]) * SUM(テーブル[数量])

を計算しています。

これは、利益のような加算、減算であれば問題ありません。利益 ＝ 売上 - 原価　の数式は、1行ごとに計算したものを最後に合計しても、総売上から総原価を引いても同じです。

ピボット テーブル レポートの計算結果が間違っていることがあるので使いたくない、という話を聞きます。「元データが汚いよね」というのも現実ですが、この行・列ラベルでの小計が優先されることを意識していない場合もあるかもしれません。

ここでメジャーに話が戻るのですが、その意味で、ピボット テーブルのメジャーの数式では、=[単価] * [数量] という数式を記入すると上述のエラーになります。明確に以下のように数式を書かなければなりません。そして、その段階で数式がおかしいことに気が付いてほしいわけです。

= SUM('テーブル'[単価]) * SUM('テーブル'[数量])

なお、受注金額の合計を求めたいのであれば、元データに単価*数量の列を追加するか、Power Query/取得と変換 のクエリエディターで計算列を追加し、あらかじめデータ モデルに計算データを取り込むことになります。Power BI Desktop の場合はデータを取り込んだ後で、計算列の追加を使ってもいいでしょう。