【続】Microsoft - Excel Power Pivot 入門講座 DAX-TABLE FUNCTION特訓編

このセクションではTABLE FUNCTIONを学びます

• TABLE FUNCTIONとは、DAXの中の１つの分類名で、DAXの成果物として値ではなく、テーブル／表を返します

• ですので、TABLE FUNCTION単独によってのみで使われることはなく、TABLE FUNCTIONの結果を使って（＝TABLE FUNCTIONの結果として出される表を使って）何かしらの計算をすることになります

• 前のセクションの最後に、SUMXを紹介しました。具体的に言うと、TABLE FUNCTIONで返された表に対し、SUMXで集計をするイメージです

• これから紹介するTABLE FUNCTION DAXは下記の通りです

  • FILTER

  • ALL

  • RELATEDTABLE

• まずは次のレクチャーにて、TABLE FUNCTIONによって、どんなテーブルが作られるのか？これを可視化します。これは単に理解のためにTABLE FUNCTIONによってできる表を再現するものです

• その後、具体的にTABLE FUNCTIONを見ていきます

• 一通りTABLE FUNCTIONを見ていった後、こんどは学習したTABLE FUNCTIONを組わせて集計をしてみますのでお楽しみに！

前のセクションでやりましたCALCULATED COLUMNに対して、CALCULATED TABLEを説明します

• CALCULATED TABLEとは、DAXによって作られたテーブルのことです

• つまり、これから学習するFILTER、ALL、RELATEDTABLEによって作るテーブルとも言えます

  • DAXによってテーブルを作成します

• TABLE FUNCTIONによる結果は、そのまま他のDAXの結果として使われ、実際に目にすることができません

• ですのでイメージをつけづらい。これもTABLE FUNCTIONを分からなくする原因の一つです

• 後のレクチャーで説明する際、理解を促進するためにTABLE FUNCTIONによりどんなテーブルがDAX上できているのか？可視化のためにテーブルを作って説明します

• ここではその元となるテーブルを作ります

• 売上データのタブを複製します。他のデータモデルに入っているワークシートと区別のために、シート見出しの色を黄色にします

FILTERの説明をします

• FILTERは、あるテーブルを指定し、そのテーブルに対し任意の条件にあったものを残したテーブルを返します

• 前のレクチャーで作ったテーブルのコピーで、単純にフィルターをかけてみます

• 商品番号が323だけを抽出したテーブルにします

• 売上合計は817,349,065

• このテーブルをDAXによって出し、SUMXにより集計させます

FILTERの説明をします

• SUMXと組み合わせて使ってみます

• ここまでは、わざわざテーブルを作りましたが、テーブルを使わずメジャー上だけで計算してみます

• 売上データの上で右クリック。メジャーの追加

• 長いDAXを書くときは、改行をうまくつかうと分かりやすくなります

• 商品＃323の売上合計 =

      SUMX(

          FILTER('売上データ1','売上データ1'[商品番号]="323"),

          '売上データ1'[受注数量]*'売上データ1'[売価（円単価）]

          )

  • もしエラーがでたら、商品番号のデータ型に注意

• これにより、テーブルを作った時と同じ結果が得られます

• メジャーの式をスクリーンショットし、売上データ（コピー）とみながら何をしたか？を確認

• FILTERにより、テーブルに文字通りフィルターをかけたい値だけを残すことができます

• ポイントは、TABLE FUNCTIONによって、実際にテーブルを作らなくてもよい、メジャーの式の中だけで存在させることができる、ということです

FILTERの応用として、リレーションシップがある、他のテーブルにある値を条件としてみます

• 以前に紹介したRELATEDを使います

• ここでは、商品カラーがレッドだけのテーブルを作り、それを集計します

• まずは、どんなテーブルを作るのか？を見てみます

  • 【コピー】売上テーブル by Filter レッド = FILTER('売上データ1', RELATED('商品マスター1'[カラー])="レッド")

  • これにより商品カラーがレッドだけのテーブルができました

• では、SUMXを使って、レッドだけの集計をします

  • 商品カラーがレッドだけの売上 =

        SUMX(

            FILTER('売上データ1',RELATED('商品マスター1'[カラー])="レッド"),

            '売上データ1'[受注数量]*'売上データ1'[売価（円単価）]

        )

• スクリーンショットでメジャーを見せつつおさらい

• ご覧のように、DAXの強みは、DAXの中にDAXを組み込むことにより、より柔軟な集計ができる点にあります

• ここまで、FILTERを見ていきました。次にALLの説明をします

ALLの説明をします

• 前のレクチャーでは、テーブルをフィルターかけて縮小しました

• 実務では逆にフィルターをかけられたテーブルを大きくしたい時がある

• 例えば全体に対する割合を出すとき

• 商品名で括られた売上のテーブルを作ります。ここに全体に対する売上割合を出していきます

• また、スライサーの影響を受けないものを作ります。テストのために国名を示すスライサーを用意します

• このような、フィルターやスライサーを無視したテーブルを作りたい時にALLを使います

• コピー 売上テーブルで見てみます。フィルターをすべて外します

  • この結果は、単に元の売上テーブルと同じです

  • ポイントは、このテーブルはどんなフィルターやスライサーが入っても変化を受けない、ということです

• ALLで返すテーブルをSUMXで集計します

  • 売上 by ALL =

         SUMX(

                ALL('売上データ'),

                '売上データ'[受注数量] * '売上データ'[売価（円単価）]

               )

• できたメジャーをテーブルに入れてみます。全部同じ値。しかも合計は一致しています

• また、国別スライサーでどう動くか？確認してみます。スライサーの影響を受けません

• 全体の金額が一致していることを確認

• では、全体に対する割合を計算する用意ができました。全体に対する割合を出します

  • 売上の全体に対する割合 =DIVIDE('売上データ'[売上 by SUMX],'売上データ'[売上 by ALL])

• これにより無事、全体に対する割合が出せました

• 国別のスライサーをかけたとしても、ALL売上合計の値は変わらないため、常に全体に対する割合を出してくれます

ここでは補足として、値フィールドの設定 / 計算の種類 / 総計に対する比率との違いを示します

• 違いは「何を全体としてとるか？」です

• ALLは常に総合計をとります

• 統計に対する比率では、その時のコンテキストを踏まえての合計をとります

• 行にセグメント、スライサーとして国名を入れてみます

• 国名を変えた時の、DAXによって算出した全体に対する割合と、総計に対する比率との差を比べます

• 実務では、スライサーで個別に区切った話をしていると、それが全体に対してどのくらいの割合なのか？を忘れてしまうことがあります。常に全体に対する割合があれば全体観を見失うことなく個別の話ができます（例. オーストラリアの電動スクーターはオーストラリア全体に対しては98%だが、全体に対してはたった3%）

ALLの応用として、扱っている商品の全体的な平均とその平均との差異を出します。こうすることで、今見ている商品が商品全体の中でどういう位置にあるのか？を把握できます

• まず、商品の平均を計算するメジャーを作ります

  • 商品単価平均 = AVERAGE('商品マスター'[売価（円単価）])

  • もし、間違えて売上データの所に作ってしまっても簡単に修正できます

    • 作ったメジャーがどのテーブルにいくか？は、メジャーツール / ホームテーブルで設定できます

• 次に、全体の商品の平均を計算するメジャーを作ります

  • 全体の商品平均価格 = CALCULATE('商品マスター'[商品単価平均],ALL('商品マスター'))

• 今度は、商品の平均と全体平均との差をとるメジャーを作ります

  • 全体平均との差異 = '商品マスター'[商品単価平均]-'商品マスター'[全体の商品平均価格]

  • ここで思わず、商品マスターの商品単価をとりがちですが、メジャー内はメジャーしかとれないことに注意です

  • （わざとメジャーの名前はいれずに）なぜかOKができない。DAX式も確認したのに。実務でよくある。メジャーの名前が入っていないから

• こうして作ったメジャーを並べてみると、価格帯の分布が見えてきます

  • 行に商品番号と商品名を入れ、受注数量を値に入れます

  • 受注数量を大きいものから小さいものに並べた時、数量と平均単価との差（＝高価格帯商品 or 低価格帯商品）を見比べます

    • スライサーによりセグメント別でも見てみます

ここでは、これまで学習したSUMX、FILTER、ALLを組み合わせて使ってみます

• FILTERとALLを組み合わせることで、特定の条件にあったテーブルを返し、それをSUMXで集計します

• ここでこう思った方いらっしゃるかもしれません「FILTERとALLって全く真逆な用途を持つDAX。組み合わせて何ができるのか？」と

• スライサーや、フィルターコンテキストなどを一旦リセットして、フィルターし直すイメージです

• コピー 売上テーブルで見てみます

  • フィルターでアメリカの地域番号を指定します。このフィルター結果を固定させるためにFILTERとALLを組み合わせます

  • よく誤解があるのが、FILTERは他のフィルターやスライサーの影響を受けない、というものです。なので、FILTERとALLを組み合わせる意味がわからんって言う人がいます....私もそうでした。商品番号 323の売上メジャーで説明します

• 実際にやってみます。例えば、前のレクチャーのケースで、今度はアメリカだけの中での % of totalで例示する場合

  • アメリカだけの売上合計 =

    　SUMX(

                 FILTER(

                             ALL('売上データ'),

                             RELATED('地域マスター'[国名])="アメリカ"

                            ),

                '売上データ'[受注数量] * '売上データ'[売価（円単価）]

               )

• では、アメリカの売上合計に対する割合を出します

  • アメリカ売上に対する売上割合 =DIVIDE('売上データ'[売上 by SUMX],'売上データ'[アメリカだけの売上])

  • こうすることでベンチマークとなる売上を設定し、それに対する割合を出すことができます

    • 今回は説明の便宜上、一番売上の大きいアメリカを選びました。実務では中堅どころが選ばれることがあります。例えばイギリス、フランスなど

  • ALLによって、常に同じ値を出すことによって、定点を決めそのからの差異という視点で分析できる

前のセクションでは、RELATEDというDAXを紹介しました。ここでは、RELATEDTABLEというTABLE FUNCTIONを紹介します

• RELATEDとの違い。RELATEDはたった１つの値を返します。RELATEDTABLEならテーブル自体を返すことができます

• さて、唐突ですが、モデルビューを見てみます。商品マスターと売上データは、One to Manyの関係になっています

• 前のレクチャーでは、売上テーブルに商品マスターからカラーの１列をもってきます

• One to Manyの関係では、RELATEDによりOneの方から、Manyの方へとデータをもってくることができます

• ちょうどVLOOKUP/XLOOKUPの要領です

• では逆にやるとどうなるか？できません。RELATEDをやろうとしても、候補すらでません

• なぜならRELATEDはたった１つの値しか返さないから、どの時の値を返したらいいか分かりません

• ここでRELATEDTABLEの出番になる。このDAXなら、テーブルごと返すことができる

• ここでは、実務で使えるケースである商品マスターに、1列足して売上データの結果を持っていきたい

• こうすることにより、商品マスターを見ることでその商品がどのくらいの売上を上げているか？が分かる

• 販売額 =

      SUMX(

          RELATEDTABLE('売上データ1'), '売上データ1'[受注数量]*'売上データ1'[売価（円単価）]

      )

• 数式の結果をレポートビューで、商品マスターで作る商品一覧の販売額と、売上データで作る販売額を作成し比較する

• 一致している

• RANKXで一列追加。RANXを列参照じゃなく、ネストで先ほど作ったものを入れる

• データ型をテキストにする。これは足したり引いたりする数字でないため

• これは常に絶対的なランキング

• 行に商品番号、商品名、カラー、販売額によるランキング、売上 by sumxを出し、売上順に並び替える

• 国でスライサーをとり、国別にランキングの順位がどうなっているか？を見る

  • レーサーFタイプは、全体では２位でも、アメリカ、カナダといった北米では人気のために上位ランクしているが、他の地域ではそんなに人気がない

  • クルーズAタイプは、北米以外で人気。上位に入っている。一方北米では他の機種が人気

    
    

COUNTROWS, FILTER, RELATEDTABLEを使ったDAXを作ってみる

• 今度は、RELATEDTABLEを使って、売上ではなく販売回数で見てみます

• COUNTROWSというDAXを使います。これにより指定のテーブルに何行あるのか？が数えることができます

• 売上、売上単価は注意がいきがちですが、では販売回数は？に着目します

• 商品マスターのテーブルをクリック

• ホーム / 新しい列

  • 販売回数 =

            COUNTROWS(

                RELATEDTABLE('売上データ')

            )

• 販売回数によるランキング列も加えてみる

• ランキングのデータ型をテキストに。これは足したり引いたりする数字ではないため

• 行に商品番号、商品名、カラー、販売回数によるランキング、売上 by sumxを出し、売上順に並び替える。先ほどと同じデータではあるが、販売回数という視点で見てみる

• なぜ販売回数が少ないのに、売上が一位なのか？

  • 注意！商品番号を出さないと、ランキングが合計されてしまう。同じ商品でも違う色別にランキングあるため

  • 実務では、顧客で売上と販売回数を比べてみると面白い。往々にして売上が多い顧客は販売回数が少ない。実は、売上以上に手間がかかっている顧客があぶり出せる

このセクションでは、カレンダーマスターをDAXで作っていきます

• これまで通常であれば、エクセルでカレンダーマスターを用意していたと思います

• それ自体は何も悪くありません

• しかし、Power Pivotタブで作るといとも簡単にできますので、ぜひみなさんのレパートリーに加えていただきたいと思います

• ここでは、カレンダーマスターを作る意義について復習します？

  • 複数のテーブルをつなげる時に必要

  • 抜けのないカレンダーが作れる（例. 売上がゼロなのとデータがないのとは違う）

  • より柔軟に日付データの加工が可能

• 逆に言えば、もしデータが一つのテーブルですべてまとまっており、日付の抜けなど気にしない、日付データに加工が不要であれば作る必要はない（でも、これから見ていくように作ること自体はすごく簡単なので、省略する理由がない）

Power Pivotタブを使ってカレンダーテーブルを作ります

• Power Pivot / 管理

• デザイン / 日付テーブル / 新規作成

• もうこれで完成です

  • 日付の範囲は、現在取り込んでいるデータの範囲を参考にとっています

    • もし日付が広くなり過ぎた場合（顧客情報などあり生年月日があるとそうなりがち）は、手動で調整する

  • デフォルトで作成された列を見てみます

  • 列名が気に入らなければ、自由に変えることができます

  • 月名、曜日名はMMM、DDDと三文字にすることで省略できます

  • 曜日の番号は、何曜日をスタートするか？を設定できます

• 次のレクチャーではDAXによりカレンダーに情報を追加します

DAXで作ったカレンダーマスターに情報を加えていき、使いやすいカレンダーマスターを作ります

• IF関数を使って、平日か週末か？を入れます

  • Weekday_or_Weekend = IF('カレンダー by CALENDAR_Dynamic_2'[Weekday] >= 6, "Weekend", "Weekday")

• 同じくIF関数を使って、暦とは異なる会計年度を入れます

  • Fiscal Year = IF('カレンダー by Calendar_3'[Month] <= 3, 'カレンダー by Calendar_3'[Year]-1, 'カレンダー by Calendar_3'[Year])

• なぜMONTH NUMBERが入っているのでしょうか？

• 理由は、次のレクチャーで説明します

ここでは、なぜ、月の名前があるのに月の番号を、そして曜日の番号を入れたのか？を説明します

• ピボットテーブルに、Monthを入れてみます

  • ここでピボットテーブルで、カレンダーの名前がデフォルトの予定表になっていることを確認。Power Pivotタブで変える

• 並びがぐちゃぐちゃになります（同じことが曜日名にあてはまる）

• 並びを設定します

• 該当列を選択し、Pivot Tableタブ / ホーム / 列で並び替え

  • 同じ処理がPower BIでもあり。Power BIのデータビューで行う

• 並び替え設定を解除することもできます。曜日を解除し、もう一度設定し直してみます

• 厄介なのが、3月に決算がある場合の月の並びです。4月が先に来て欲しいです

  • Year, FY, Monthを行にとり、どのような並びになるか？を示す

• 3月に決算がある場合の並び替えを次のレクチャーで見ていきます

3月に決算を迎える会計年度の場合の月の並び替えをします

• 新しい列を作ります

  • FY Month Num=IF('Calendar'[Month Num] <=3, 'Calendar'[Month Num]+9, 'Calendar'[Month Num]-3)

• これにより、会計年度の場合の並びができました

• ホーム / 列の並び替えで設定します

• これで4月をスタートする並びに設定できました

• 売上を入れてみます。なぜか同じ値が出てきます

• これはカレンダーテーブルを作った際によくあるミスです

• 次のレクチャーで原因は何か？を説明します

前のレクチャーの種明かしをします。テーブルの数字が同じ数字なのはなぜか？

• リレーションシップを組んでないからです

• ばからしく思うかもしれませんし、いい加減引っ張りすぎ！と思うかもしれません

• 実務ではよくあります。印象に残っていると忘れません

• エクセルでカレンダーマスターを作った時は、データの取り込みをするのでその一環でリレーションシップを組むが、なぜかDAXで作った場合は忘れがち

• あえてここで紹介したので、これを見たみなさんは、実務で同じことが起こったらすぐピンときます