計算列と計算フィールドの使い方 (英語)

Power Pivot の使用方法を初めて学んだとき、ほとんどのユーザーは、真の力は何らかの方法で結果を集計または計算することにあります。 データに数値を含む列がある場合は、ピボットテーブルまたは Power View のフィールド リストで選択することで簡単に集計できます。 本質的に、数値であるため、自動的に合計、平均化、カウント、または選択した集計の種類になります。 これは暗黙的なメジャーと呼ばれます。 暗黙的なメジャーは、迅速かつ簡単に集計するのに適していますが、制限があり、ほとんどの場合、明示的な メジャー と 計算列でそれらの制限を克服できます。

まず、集計列を使用して、"商品" テーブルの行ごとに新しいテキスト値を追加する例を見てみましょう。 [商品] テーブルの各行には、販売する各製品に関するあらゆる種類の情報が含まれています。 製品名、色、サイズ、ディーラー価格などの列があります。 ProductCategoryName 列を含む Product Category という名前の別の関連テーブルがあります。 ここでは、[商品] テーブルの各商品に、[商品カテゴリー] テーブルの商品カテゴリ名を含める必要があります。 製品テーブルで、次のように製品カテゴリという名前の集計列を作成できます。

新しい製品カテゴリの数式では RELATED DAX 関数を使って、関連テーブルである製品カテゴリにある製品カテゴリ名の列から値を取得し、その値を製品テーブルの各製品 (各行) に入力しています。

これはすぐれた例であり、集計列を使って固定値を行ごとに追加し、ピボットテーブルの行、列、フィルター領域または Power View レポートで使えるようにする方法を示しています。

製品カテゴリの利益率を計算する別の例を作成しましょう。 これは、多くのチュートリアルでも一般的なシナリオです。 データ モデルには、トランザクション データを含む Sales テーブルがあり、Sales テーブルと Product Category テーブルの間にはリレーションシップがあります。 売上テーブルには、売上金額を含む列と、コストを含む別の列があります。

次のように、売上高の列の値から COGS 列の値を差し引いて、利益金額を行ごとに計算する集計列を作成できます。

ここでピボットテーブルを作成して製品カテゴリ フィールドを列の領域に、新しい利益フィールドを値の領域にドラッグできます (PowerPivot のテーブルの列は、ピボットテーブルのフィールド一覧のフィールドです)。 結果は、合計利益という名前の暗黙的メジャーです。 これは、さまざまな製品カテゴリごとに利益列の値を集計した金額です。 結果は次のように表示されます。

この場合、利益は値のフィールドとしてのみ意味があります。 利益を列の領域に配置すると、ピボットテーブルは次のようになります。

利益フィールドは、列、行、フィルターの領域に配置しても、有用な情報を提供しません。 値の領域の集計値としてのみ意味があります。

これまでに、利益という名前の列を作成して、売上テーブルの行ごとに利益率を計算しました。 次に、利益をピボットテーブルの値の領域に追加すると、暗黙的メジャーが自動的に作成され、結果が製品カテゴリごとに計算されました。 製品カテゴリの利益を実際には 2 回計算したのではないかと疑問に思った人は正解です。 最初に売上テーブルの行ごとに利益を計算し、利益を値の領域に追加して、製品カテゴリごとに集計しました。 利益の集計列を作成する必要はなかったのではないかと思った人も正解です。 では、集計列を作成せずに利益を計算する方法はあるのでしょうか。

利益は、実際には明示的メジャーとして、さらに適切に計算されます。

とりあえず、利益の集計列は売上テーブルに残し、製品カテゴリはピボットテーブルの列に、利益は値に残して、結果を比較することにします。

Sales テーブルの計算領域で、(名前の競合を避けるために) Total Profit という名前のメジャーを作成します。 最終的には、これまでと同じ結果になりますが、利益の集計列はできません。

まず、[売上高] テーブルで [売上高] 列を選択し、[オート SUM] をクリックして明示的な [売上高の合計 ] メジャーを作成します。 明示的メジャーは、Power Pivot のテーブルの計算領域に作成するものであることに注意してください。 COGS 列も同様に操作します。 識別しやすいように、これらの Total SalesAmount と Total COGS の名前を変更します。

次の数式を使って、別のメジャーを作成します。

総利益:=[売上高の合計] - [COGS の合計]

総利益の新しいメジャーの書式を通貨に変更した後で、ピボットテーブルに追加できます。

新しい [総利益] メジャーは、[利益] の計算列を作成して [VALUES] に配置した場合と同じ結果を返すことがわかります。 違いは、ピボットテーブル用に選択したフィールドのみをその時点で計算しているため、総利益測定ははるかに効率的で、データ モデルをよりすっきりとスリムにすることです。 結局のところ、Profit の計算列は本当に必要ありません。

この最後の部分が重要な理由 集計列はデータ モデルにデータを追加し、データはメモリを占有します。 データ モデルを更新すると、[利益] 列のすべての値を再計算するための処理リソースも必要になります。 ピボットテーブルで利益を表示したいフィールド (製品カテゴリ、地域、日付など) を選択するときに利益を計算したいので、このようなリソースを使用する必要はありません。

別の例を挙げてみましょう。 この例では集計列で結果が作成され、その結果は一見正しいようですが….

この例では、売上金額を総売上のパーセンテージとして計算します。 次のように、売上 (%) という名前の集計列を売上テーブルに作成します。

この数式では、売上テーブルの行ごとに、売上高列の金額が、売上高列のすべての金額の合計で割られます。

ピボットテーブルを作成し、製品カテゴリを列に追加して、新しい売上 (%) 列を選んで値に配置すると、製品カテゴリごとに売上 (%) の合計が計算されます。

よろしいでしょうか。 ここまでは正しいように見えます。 しかし、スライサーを追加してみましょう。 カレンダー年度を追加して、年を選びます。 この場合は 2007 を選びます。 次のような結果になります。

一見すると、これはまだ正しいように見えるかもしれません。 しかし、2007年の各製品カテゴリの総売上の割合を知りたいので、割合は実際には100%であるはずです。 では、何が問題だったのでしょうか?

売上 (%) 列では、売上高列の値を売上高列のすべての値の合計で割って行ごとに割合を計算します。 集計列の値は固定です。 テーブルの行ごとの、変更できない結果です。 売上 (%) をピボットテーブルに追加すると、売上高列のすべての値の合計として集計されます。 売上 (%) 列のすべての値の合計は、常に 100% です。

売上 (%) 集計列は、役に立たないので削除できます。 その代わりにメジャーを作成すると、どのフィルターやスライサーを適用しても、総売上の割合は正しく計算されます。

前に作成した売上高合計のメジャーは、売上高列を単純に合計したものでした。 総利益のメジャーでは、この列を引数として使いましたが、新しい集計フィールドでも引数として再び使います。

次の数式を使って、新しいメジャーを作成します。

売上の合計 (%):=([売上高の合計]) / CALCULATE([売上高の合計], ALLSELECTED())

この数式は次のようになります。 ピボットテーブルで定義されているフィルター以外の列または行フィルターを使用せずに、Total SalesAmount の結果を SalesAmount の合計で除算します。

新しい売上の合計 (%) をピボットテーブルに追加すると、次のようになります。

適切になったようです。 製品カテゴリごとの売上の合計 (%) は、2007 年の総売上の割合として計算されます。 カレンダー年度スライサーで別の年を選んだり、複数年を選んだりした場合、製品カテゴリのパーセンテージは新しくなりますが、総計は 100% になります。 別のスライサーやフィルターを追加することもできます。 どのスライサーやフィルターを適用しても、売上の合計 (%) メジャーでは、常に総売上のパーセンテージが算出されます。 メジャーを使うと、結果は常に、列と行のフィールド、および適用されるフィルターやスライサーによって決まるコンテキストに従って計算されます。 これがメジャーの機能です。

次のガイドラインは、集計列とメジャーのどちらが特定の計算ニーズに適しているかを判断するときに役立ちます。

集計列を使う

• 新しいデータをピボットテーブルの行、列、またはフィルターに表示したり、Power View 視覚エフェクトの軸、凡例、または [タイル付け] に表示したりする場合は、集計列を使用する必要があります。 データの通常の列と同じように、集計列はどの領域でもフィールドとして使用でき、集計列が数値である場合は、値でも集計できます。
• 新しいデータを行の固定値にする場合。 たとえば、日付テーブルがあって日付の列が含まれており、別の列に月の番号のみを含める必要があるとします。 集計列を作成すると、日付列の日付から月の番号のみを計算できます。 たとえば、=MONTH(‘日付’[日付]) というようにします。
• テーブルの行ごとにテキスト値を追加する場合は、集計列を使います。 テキスト値を含むフィールドは、値で集計できません。 たとえば、=FORMAT('日付'[日付],"mmmm") では、日付テーブルの日付列の日付ごとに、月の名前を取得できます。

メジャーを使う

• 計算の結果が、ピボットテーブルで選んだその他のフィールドに常に依存する場合。
• ある種のフィルターに基づいてカウントを計算したり、前年比や分散を計算したりというように、より複雑な計算を行う必要がある場合は、集計フィールドを使います。
• ブックのサイズを最小限にしてパフォーマンスを最大にする場合は、可能な限り多くの計算をメジャーとして作成します。 多くの場合は、すべての計算をメジャーにすると、ブックのサイズが著しく小さくなり、更新時間を短縮できます。

利益列で行ったように集計列を作成して、ピボットテーブルやレポートで集計することに、まったく問題がないことに注意してください。 実際にこれは本当に適切であり、簡単に習得して独自の計算を作成できるようになります。 Power Pivot の非常に強力なこの 2 つの機能を深く理解するようになると、できる限り効率的で正確なデータ モデルを作成できるようになります。 ここで習得したことが役立つことを願っています。 他にも、本当に役に立つ、すばらしいリソースがあります。 DAX 数式のコンテキスト、Power Pivot の集計、DAX リソース センターは、ほんの一部です。 また、もう少し高度であり、会計および財務の専門家向けですが、 Excel での Microsoft Power Pivot を使用した損益データのモデリングと分析 サンプルには、優れたデータ モデリングと数式の例が満載です。