@ishiayaya さんと@yugoes1021 が開催されているPower BI Weekly News。
いつも楽しく為になる話を聴かせていただいている訳なのですが、以下、第10回の参加者からの質問が興味深かったので試してみたメモです。
以降、タイムインテリジェンス関数をカレンダーテーブル無しに使う、不要なパラーメータを関数に与えている、といった点が始めに投稿した内容を含んでおり、その旨アドバイスいただいた為一旦削除しての再投稿となります。
質問 ※簡略化しています
Power BIでExcelのSUMIFSのようなことをどうやったら実現出来ますか？
・商品の売上一覧（日付、商品名、販売額）
| 商品名 | 日付 | 販売額 |
|---|---|---|
| A | 2021/12/10 | 5000 |
| A | 2021/12/11 | 5000 |
| A | 2021/12/12 | 5000 |
| A | 2021/12/13 | 5000 |
| A | 2021/12/14 | 5000 |
| A | 2021/12/15 | 5000 |
| A | 2021/12/16 | 5000 |
| A | 2021/12/17 | 5000 |
| A | 2021/12/18 | 5000 |
| A | 2021/12/19 | 5000 |
| A | 2021/12/20 | 5000 |
| B | 2021/12/10 | 10000 |
| B | 2021/12/11 | 10000 |
| B | 2021/12/12 | 10000 |
| B | 2021/12/13 | 10000 |
| B | 2021/12/14 | 10000 |
| B | 2021/12/15 | 10000 |
| B | 2021/12/16 | 10000 |
| B | 2021/12/17 | 10000 |
| B | 2021/12/18 | 10000 |
| B | 2021/12/19 | 10000 |
| B | 2021/12/20 | 10000 |
| C | 2021/12/10 | 20000 |
| C | 2021/12/11 | 20000 |
| C | 2021/12/12 | 20000 |
| C | 2021/12/13 | 20000 |
| C | 2021/12/14 | 20000 |
| C | 2021/12/15 | 20000 |
| C | 2021/12/16 | 20000 |
| C | 2021/12/17 | 20000 |
| C | 2021/12/18 | 20000 |
| C | 2021/12/19 | 20000 |
| C | 2021/12/20 | 20000 |
| D | 2021/12/10 | 40000 |
| D | 2021/12/11 | 40000 |
| D | 2021/12/12 | 40000 |
| D | 2021/12/13 | 40000 |
| D | 2021/12/14 | 40000 |
| D | 2021/12/15 | 40000 |
| D | 2021/12/16 | 40000 |
| D | 2021/12/17 | 40000 |
| D | 2021/12/18 | 40000 |
| D | 2021/12/19 | 40000 |
| D | 2021/12/20 | 40000 |
があり、これに対して、商品名×開始日～終了日の集計を実施したい、というもの。こんな感じに。
| テスト名称 | 商品名 | 開始日 | 終了日 | 商品別売上合計 |
|---|---|---|---|---|
| A_1 | A | 2021/12/12 | 2021/12/14 | 15000 |
| A_2 | A | 2021/12/18 | 2021/12/20 | 15000 |
| B_1 | B | 2021/12/13 | 2021/12/15 | 30000 |
| B_2 | B | 2021/12/19 | 2021/12/21 | 20000 |
| C_1 | C | 2021/12/14 | 2021/12/16 | 60000 |
| C_2 | C | 2021/12/20 | 2021/12/22 | 20000 |
| D_1 | D | 2021/12/16 | 2021/12/17 | 80000 |
| D_2 | D | 2021/12/20 | 2021/12/22 | 40000 |
で、元のテストの情報はどうなっているのかと、恐らくこんな感じなのではと思われる。
| テスト名称 | 開始日 | 終了日 |
|---|---|---|
| A_1 | 2021/12/12 | 2021/12/14 |
| A_2 | 2021/12/18 | 2021/12/20 |
| B_1 | 2021/12/13 | 2021/12/15 |
| B_2 | 2021/12/19 | 2021/12/21 |
| C_1 | 2021/12/14 | 2021/12/16 |
| C_2 | 2021/12/20 | 2021/12/22 |
| D_1 | 2021/12/16 | 2021/12/17 |
| D_2 | 2021/12/20 | 2021/12/22 |
※Point: テスト名称の一部が、商品名
で、どう試してみたか？
方法はいくつもあるだろうし、これが良い方法なのか知らない（むしろ悪そうな気がする）が、ひとまず以下のような方法で実現出来ました。
1. [Power Query] 売上一覧をロード
2. [Power Query] 売上一覧の参照から、商品名列以外を削除＆商品名の重複を削除し商品クエリを作る
3. [Power Query] テスト情報をロード
4. [Power Query] テスト情報のテスト名称の1文字目を切り出し、「商品名」列を作る
「テスト名称列」列を選択し、「例から列を追加する」で、テスト名称の1文字目「A」を入力して作成
5. [Power Query] 閉じて適用
6. [データ] カレンダーを作る。
CALENDARAUTO()を使い、カレンダーテーブルを作る。
7. [モデル] 「商品名」列を使い、テスト情報と商品、売上一覧と商品のリレーションシップを作る。また、カレンダーテーブルの日付と売上一覧とのリレーションシップも作る。
8. [レポート] 売上一覧に、合計値のメジャー「売上合計」を作る。
9. [データ] DAX計算式でテスト情報に売上合計の列を作る。
Calculate関数で、日付の期間と商品名をフィルタ条件にして、合計を算出する。
テスト期間の売上合計 = CALCULATE('売上一覧'[売上合計],DATESBETWEEN('Calendar'[Date],'テスト情報'[開始日],'テスト情報'[終了日]) )
10. [レポート] テーブルビジュアルを使い、テスト情報を配置する。
別アプローチ：テスト実績のファクトテーブル作成で対応する
Power BIでのモデリングに関しては、一般的に列のマージを多用することは、データ保持の効率性やパフォーマンスの観点から推奨されていません。
ですが、上記のモデルはスタースキーマで無いため使いづらいものとなっています。
スキーマをよりシンプルにする為に、列のマージを行い、テスト実績に売上金額を結合させ、売上一覧はデータモデルとして読み込まない、というアプローチなんてのもあるのかな、と思います（以下）。
1. [Power Query] テスト情報のテスト期間を日付の一覧に展開する。
= Table.AddColumn(区切り記号の前に挿入されたテキスト, "日付", each List.Dates([開始日], Duration.Days([終了日]-[開始日])+1, #duration(1,0,0,0)))
→ 期間のFrom,Toを使い日付Listの列を追加する。
→ 日付Listの列を展開すると、上記のようになる。
→ 「開始日」「終了日」は不要のため列を削除
2. [Power Query] テスト情報に売上一覧の金額をマージさせる
→ 左外部結合で、売上の金額をテスト情報にマージさせる。
3. [Power Query] 売上一覧がモデルとして作られないよう「読み込みを有効にする」チェックを外す。
4. [Power Query] 閉じて適用
→ モデルは上記のような形で作られる。
5. [レポート] テスト情報に、以下のメジャーを追加
開始日 = MIN('テスト情報'[日付])
終了日 = MAX('テスト情報'[日付])
6. [レポート] テーブルビジュアルを作る。
以上