全体とメンバー別のグラフを作成してみて事件発生
こんな感じの、いつ、誰が、誰に指名されたかというトランザクションデータがあるとします。
このデータから、今年初めて指名してくれたお客の人数をカウントしたい！という要望があったとします。
これを、こうやって今年以降の日付でフィルターしてカウントすればいいじゃん！とこんなメジャーを書きました、が。
当年指名人数 = CALCULATE(DISTINCTCOUNT('指名履歴'[お客]),'指名履歴'[日付]>=DATE(2019,1,1))
このメジャーだと、今年来てくれたお客をカウントしているだけで、今年も去年も来てくれたお客も含まれてしまうので、
当年初指名人数=(お客の人数)-(去年のお客の人数)
と考えて
当年初指名人数 = DISTINCTCOUNT('指名履歴'[お客])
-CALCULATE(DISTINCTCOUNT('指名履歴'[お客]),'指名履歴'[日付]<DATE(2019,1,1))
こんなメジャーで算出することにしました。
$\tiny{※今年のお客から去年のお客を引くと、今年のお客から去年しか来ていないお客も引いてしまうので注意です。}$
で、グラフ。まずは全体。
案外少ない。苦戦してますね。
では、メンバー別に見てみましょう。
え・・・・全体で4人なのに、メンバー別にすると16人！何故？？？
捜査開始
とりあえず明細を見て確認と思い、グラフをテーブルに変えてみる。
え？ええ？？なんでこれで合計4？？
この時点で、ちょぴり「バグかも・・・・？」とかも思ったりもするが、甘い。たいがいのことは自分に問題がある。
で、明細。
ここでクイズ①。原因が分かりますか？
↓
↓
↓
↓
↓
↓
↓
↓
↓
これは簡単。エさま、ビさま、総さまはダブルカウントされてしまっていますね。
ただ、これだけが原因ではなさそうです。ダブルカウントされている3人を除外しても、13人。全体の4人とはまだ乖離があります。
そこで、全体で今年初とカウントされている4人が誰なのか見てみます。
そして、該当しないお客の指名履歴の明細を見てみます。例えば市さま。
市さま、色々とご指名されていますが、ここでクイズ②。原因が分かりましたでしょうか？
↓
↓
↓
↓
↓
↓
↓
↓
↓
わたし的にはここが結構落とし穴で、実データではなかなか発見ができませんでした・・・・・
このサンプルデータだとすぐわかると思うのですが、アッピーにとっては市さまは今年初めて指名してくれたお客なので、メンバー別にするとカウントされてしまうのですが、去年アストロやアインシュタインが指名されているので、全体にとっては今年初めてのお客ではないのでした。
結局Power Queryで・・・・
これを解決するメジャーが考え付かず、結局Power Queryでゴリゴリグループ化することに。
①次の3つのクエリを作成する。
-当年初指名：指名履歴を今年の日付でフィルターし、お客ごとにグループ化、日付の最小を集計する。
-当年初指名メンバー別：指名履歴を今年の日付でフィルターし、お客ごと、メンバーごとにグループ化、日付の最小を集計する。
-前年指名：指名履歴を去年の日付でフィルターし、お客ごとにグループ化、日付の最小を集計する。
②当年初指名に当年初指名メンバー別をマージ。
照合列はお客と日付。
メンバーを展開
③当年初指名に前年指名をマージ。
照合列は日付。
前年指名日付を展開
④前年指名日付がnullのお客だけをフィルター
最終的にこんなクエリができます。
で、グラフはこんな感じ。
全体の合計とメンバーの合計が合致しています。
アインシュタイン、アッピー、クラウディ、ブレイズが1人ずつ。
一番人気のアストロがまさかの0人。常連ばかりにかまけてないで、新規獲得活動も必要ですね。
事件振り返り
実はずっと公開していたレポート、全然使われずに事件が放置されていて、三連休前の金曜日に急に「アレ使うから、体裁整えておいて」と言われたのが金曜日の15時ごろ。
事件が発覚したのが16時ごろ。
落ち着いて捜査すればすぐに原因が分かったと思うのですが、タイムリミットが近づくに比例して焦りみが急上昇、結局三連休に持ち越したという残念な結果でした。
サンプルみたいに分かりやすければ作成した時にすぐわかると思うのですが、多分作成した時点では原因①も②も発生していなかったので気が付かなかったものと推測されます・・・・。（言い訳）
こういうことがあったという自分的備忘録とともに、もしもっといい集計方法がありましたらご教授ください。