Power BI の レポートに利用できるフィルターにはいくつかあり、集計される範囲を指定することができる。
- レポート全体に影響する レポート レベル フィルター
- ページごとに設定できる ページ レベル フィルター
- ビジュアルごとに設定できる ビジュアル レベル フィルター
- ドリルスルー フィルター(特定の用途だけれども...)
また、ディメンジョン テーブルである "日付テーブル" の "年"フィールドを スライサー ビジュアルに使用し、該当する"年"ごとの集計などはできる。
だけど、ユーザーが選択した範囲で集計できるレポートはどうしたらよいのか？ということ。
デモファイル もありますよ。
先にポイント
フィルター 機能では 列(フィールド) を指定しその値をもってフィルターを適用するのだけど、ビジュアル レベル フィルターは メジャー(measure) を使用することができる。対象の範囲にあるかをこれで判定すればよい。そして、リレーションシップ が設定されない パラメーター用のテーブルが必要。
レポートの仕様
Power BI レポート の ページ に配置した スライサーで集計する範囲を指定する。
- 範囲の指定は 年月(YYYY-MM) と 月数 で
- 集計するのは 月ごとの集計 と その累計額
データモデルの概要
受注日ごとの受注額を含まれる "受注"テーブル と 時系列の集計に必要な 日付テーブル "カレンダー" テーブルがある。
それらのリレーションシップは "カレンダー" の日付列 "Date" <- 1 対 多 -> "受注" の日付列 "受注日"
ファクト テーブル：受注
| ... | 受注日 | 明細行計 | ... |
|---|---|---|---|
| ... | ... | ... | ... |
| ... | 2015-1-11 | \30,000 | ... |
| ... | ... | ... | ... |
| ... | 2017-12-11 | \50,000 | ... |
| ... | 2017-11-30 | \80,000 | ... |
| ... | ... | ... | ... |
ディメンジョン テーブル：カレンダー
CALENDARAUTO 関数 を使用し、集計に使用する "年月"列を追加
カレンダー =
ADDCOLUMNS (
CALENDARAUTO (),
"年", YEAR ( [Date] ),
"月", FORMAT ( [Date], "mmm" ),
"年月", FORMAT ( [Date], "yyyy-mm" ),
"MonthNumber", MONTH ( [Date] )
)
ここから本題に関する作業
パラメーター用のテーブルを追加
テーブルには、年月(YYYY-MM) と 月数 に使用する列を用意
テーブル Period
Period =
SUMMARIZECOLUMNS( 'カレンダー'[年月], "日付", MIN( 'カレンダー'[Date] ) )
テーブル Durations
Durations =
SELECTCOLUMNS( GENERATESERIES( 3, 12 ), "Months", [Value] )
メジャーを作成
受注額を集計するメジャー
ファクトテーブルに計算済みの列があるのでそのまま SUM 関数で集計
受注額 = SUM ( '受注'[明細行計] )
選択された月数を計算するメジャー
SELECTEDVALUE 関数 を使用して 選択された値を計算
フィルターが適用された 'Durations'[Months] 列が単一の値となったとき、その値を返す。一意の値を特定できない場合は ( 6 ) とした。
SelectedDuration = SELECTEDVALUE( 'Durations'[Months], 6 )
この関数は比較的新しくて、以前の書き方であれば以下の通り。
SelectedDuration =
IF( HASONEVALUE( 'Durations'[Months]), VALUES( 'Durations'[Months] ), 6 )
選択した範囲の開始日を計算するメジャー
ここでも SELECTEDVALUE 関数 を使用して 選択された値を計算
FILTER 関数 では、テーブル"Period" に 選択した年月を条件にフィルターを適用。そして、[日付]列を参照するために MAX 関数 を使用した。列からスカラー値を取り出すためのものなので、MIN 関数 でも同じ結果になる。なぜなら、フィルタ適用後1行のテーブルになるから。
FirstDateInPeriod =
CALCULATE(
MAX( 'Period'[日付] ),
FILTER( 'Period', 'Period'[年月] = SELECTEDVALUE( 'Period'[年月] ) )
)
選択した範囲であることを計算するメジャー
選択した範囲で集計するビジュアルの ビジュアル レベル フィルター に使用
ディメンジョン テーブル の 'カレンダー' を基に集計が行われる場合、'カレンダー' すべての行ごと(日付ごと)に対しこのメジャーで評価することができる。
IsSelectedDates =
IF(
MAX( 'カレンダー'[Date] ) >= [FirstDateInPeriod]
&& MAX( 'カレンダー'[Date] ) <= EOMONTH( [FirstDateInPeriod], [SelectedDuration] - 1)
, 1, 0
)
選択した期間の開始日 [FirstDateInPeriod] と 期間の月数 [SelectedDuration] で表現できる範囲に 'カレンダー'[Date] の日付が含まれるとき( 1 )、そうでないときを ( 0 ) とした。
動作の確認
レポートページの作成
必要なビジュアルは、
- 期間を指定するための スライサー ビジュアル
- 特定した期間で集計するお好みのビジュアル
の 2種類
スライサー
2つのスライサーは 範囲の開始年月 と その期間(月数)。
スライサーの [フィールド]には それぞれ 'Period'[年月] と 'Durations'[Months]
お好みのビジュアル
棒グラフ(積み上げ縦棒グラフなど)を例にすると、"軸" を 'カレンダー'[年月] 列、"値" は メジャー [受注額]
メジャー [IsSelectedDates] を ビジュアル レベル フィルター に配置し、条件の値を ( 1 ) にしてフィルターの適用
選択された範囲での累計
受注額の累計金額も表示したいが、独自のメジャーを定義することが必要
タイム インテリジェンス 関数のひとつ TOTALYTD 関数 とかが便利なのだけど、これだと累計の開始区切りの手当が必要だし、12ヵ月を超える累計ということもあるし。
選択した範囲で受注額の累計を計算するメジャー
'カレンダー'[年月] と メジャー [受注額] のテーブルがあったとして、
| 'カレンダー'[年月] | [受注額] |
|---|---|
| ... | ... |
| 2017-01 | |
| 2017-02 | |
| 2017-03 | |
| ... | ... |
"2017-01" の行 で集計対象となる 'カレンダー'[日付] は 2017-01-01 から 2017-01-31 の範囲となり、メジャー [受注額] はこの範囲で評価が行われる。次の行以降でも同様に 行ごと に評価されるので、月ごとの集計表となる。
[受注額]を月ごと累計したいので、行ごとに適用される範囲は置き換える必要がある。行ごとで適用されている範囲を置き換えるには CALCULATE 関数 を使用する。
受注額CumulativeTotalInPeriod =
VAR CumulativeTotal =
CALCULATE(
[受注額],
FILTER(
ALL( 'カレンダー'[Date] ),
'カレンダー'[Date] >= [FirstDateInPeriod]
&& 'カレンダー'[Date] <= MAX( 'カレンダー'[Date] )
)
)
RETURN
CumulativeTotal
-------------------------------------------------------------
// IF(
// COUNTROWS( FILTERS( 'カレンダー'[年月] ) ) = 1
// && NOT( ISBLANK( [受注額] ) )
// , CumulativeTotal
// )
FILTER 関数 で 置き換える集計範囲(フィルター テーブル)を定義
FILTER(
ALL( 'カレンダー'[Date] ),
'カレンダー'[Date] >= [FirstDateInPeriod]
&& 'カレンダー'[Date] <= MAX( 'カレンダー'[Date] )
)
ALL( 'カレンダー'[Date] )
各行が集計されるときすでに 'カレンダー'[Date] 列の範囲が適用されているので、ALL 関数 でその範囲を解除
'カレンダー'[Date] >= [FirstDateInPeriod]
'カレンダー'[Date] が 指定する集計範囲の開始日 [FirstDateInPeriod] 以上(日付なので当日含む以降)
'カレンダー'[Date] <= MAX( 'カレンダー'[Date] )
各行で適用されている範囲の最大値 MAX( 'カレンダー'[Date] ) 以下(日付なので当日含む以前ということ)
左辺の 'カレンダー'[Date] は ALL( 'カレンダー'[Date] ) で 評価されたテーブルの 'カレンダー'[Date] 列
FILTER(
ALL( 'カレンダー'[Date] ),
[Date] >= [FirstDateInPeriod]
&& [Date] <= MAX( 'カレンダー'[Date] )
)
って表記したら少し区別つくのかな。機会があればそのうち整理。
できあがり
デモファイル はご自由にどうぞ。