計算列と計算フィールドの使い方

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

最初に、計算列を使用して、Product という名前のテーブルの各行に新しいテキスト値を追加する例を見てみしましょう。 [製品] テーブルの各行には、販売する各製品に関するすべての種類の情報が含まれている。 製品名、色、サイズ、販売店価格などの列があります。 ProductCategoryName 列を含む Product Category という名前の別の関連テーブルがあります。 必要な情報は、Product テーブル内の各プロダクトに対して、Product Category テーブルの製品カテゴリ名を含める場合です。 Product テーブルでは、次のように Product Category という名前の集計列を作成できます。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

新しい総利益メジャーは、利益計算列を作成して VALUES に配置する場合と同じ結果を返すのを確認できます。 違いは、総利益メジャーの方がはるかに効率的であり、ピボットテーブルに選択したフィールドに対してだけ、その時点で計算を行うので、データ モデルの効率が向上し、より効率的になります。 結局のところ、その利益計算列は本当に必要ない。

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

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

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

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

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

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

一見すると、これは正しく表示される場合があります。 ただし、2007 年の各製品カテゴリの売上合計に対する割合を知りたいので、パーセンテージは実際には合計 100% である必要があります。 では、何が問題でしたか?

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

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

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

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

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

この数式では、売上高の合計の結果を売上高の合計で割り、列や行のフィルターのうちピボットテーブルで定義されているものを適用します。

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

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

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

集計列を使う

• 新しいデータをピボットテーブルの行、列、フィルター、または Power View 視覚エフェクトの軸、凡例、または TILE BY に表示する場合は、集計列を使用する必要があります。 データの通常の列と同じように、集計列はどの領域でもフィールドとして使用でき、集計列が数値である場合は、値でも集計できます。

• 新しいデータを行の固定値にする場合。 たとえば、日付テーブルがあって日付の列が含まれており、別の列に月の番号のみを含める必要があるとします。 集計列を作成すると、日付列の日付から月の番号のみを計算できます。 たとえば、=MONTH(‘日付’[日付]) というようにします。

• テーブルの行ごとにテキスト値を追加する場合は、集計列を使います。 テキスト値を含むフィールドは、値で集計できません。 たとえば、=FORMAT('日付'[日付],"mmmm") では、日付テーブルの日付列の日付ごとに、月の名前を取得できます。

メジャーを使う

• 計算の結果が、ピボットテーブルで選んだその他のフィールドに常に依存する場合。

• ある種のフィルターに基づいてカウントを計算したり、前年比や分散を計算したりというように、より複雑な計算を行う必要がある場合は、集計フィールドを使います。

• ブックのサイズを最小限にしてパフォーマンスを最大にする場合は、可能な限り多くの計算をメジャーとして作成します。 多くの場合は、すべての計算をメジャーにすると、ブックのサイズが著しく小さくなり、更新時間を短縮できます。

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