Edited at

ユーザーが選択した任意の範囲で集計するには

More than 1 year has passed since last update.

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

Period = 

SUMMARIZECOLUMNS( 'カレンダー'[年月], "日付", MIN( 'カレンダー'[Date] ) )

2017-12-13_12-41-37.png


テーブル Durations


テーブル:Durations

Durations =

SELECTCOLUMNS( GENERATESERIES( 3, 12 ), "Months", [Value] )

2017-12-13_12-41-29.png


メジャーを作成


受注額を集計するメジャー

ファクトテーブルに計算済みの列があるのでそのまま SUM 関数で集計


メジャー:受注額

受注額 = SUM ( '受注'[明細行計] )



選択された月数を計算するメジャー

SELECTEDVALUE 関数 を使用して 選択された値を計算

フィルターが適用された 'Durations'[Months] 列が単一の値となったとき、その値を返す。一意の値を特定できない場合は ( 6 ) とした。


メジャー:SelectedDuration

SelectedDuration = SELECTEDVALUE( 'Durations'[Months], 6 )


この関数は比較的新しくて、以前の書き方であれば以下の通り。

SelectedDuration =

IF( HASONEVALUE( 'Durations'[Months]), VALUES( 'Durations'[Months] ), 6 )


選択した範囲の開始日を計算するメジャー

ここでも SELECTEDVALUE 関数 を使用して 選択された値を計算

FILTER 関数 では、テーブル"Period" に 選択した年月を条件にフィルターを適用。そして、[日付]列を参照するために MAX 関数 を使用した。列からスカラー値を取り出すためのものなので、MIN 関数 でも同じ結果になる。なぜなら、フィルタ適用後1行のテーブルになるから。


メジャー:FirstDateInPeriod

FirstDateInPeriod = 

CALCULATE(
MAX( 'Period'[日付] ),
FILTER( 'Period', 'Period'[年月] = SELECTEDVALUE( 'Period'[年月] ) )
)


選択した範囲であることを計算するメジャー

選択した範囲で集計するビジュアルの ビジュアル レベル フィルター に使用

ディメンジョン テーブル の 'カレンダー' を基に集計が行われる場合、'カレンダー' すべての行ごと(日付ごと)に対しこのメジャーで評価することができる。


メジャー:IsSelectedDates

IsSelectedDates = 

IF(
MAX( 'カレンダー'[Date] ) >= [FirstDateInPeriod]
&& MAX( 'カレンダー'[Date] ) <= EOMONTH( [FirstDateInPeriod], [SelectedDuration] - 1)
, 1, 0
)

選択した期間の開始日 [FirstDateInPeriod] と 期間の月数 [SelectedDuration] で表現できる範囲に 'カレンダー'[Date] の日付が含まれるとき( 1 )、そうでないときを ( 0 ) とした。


動作の確認

2017-12-13_14-52-23.png


レポートページの作成

必要なビジュアルは、


  • 期間を指定するための スライサー ビジュアル

  • 特定した期間で集計するお好みのビジュアル

の 2種類


スライサー

2つのスライサーは 範囲の開始年月 と その期間(月数)。

スライサーの [フィールド]には それぞれ 'Period'[年月] と 'Durations'[Months]


お好みのビジュアル

棒グラフ(積み上げ縦棒グラフなど)を例にすると、"軸" を 'カレンダー'[年月] 列、"値" は メジャー [受注額]

メジャー [IsSelectedDates] を ビジュアル レベル フィルター に配置し、条件の値を ( 1 ) にしてフィルターの適用

2017-12-13_15-05-13.png

2017-12-13_13-12-03.png


選択された範囲での累計

受注額の累計金額も表示したいが、独自のメジャーを定義することが必要

タイム インテリジェンス 関数のひとつ TOTALYTD 関数 とかが便利なのだけど、これだと累計の開始区切りの手当が必要だし、12ヵ月を超える累計ということもあるし。


選択した範囲で受注額の累計を計算するメジャー

'カレンダー'[年月] と メジャー [受注額] のテーブルがあったとして、

'カレンダー'[年月]
[受注額]

...
...

2017-01

2017-02

2017-03

...
...

"2017-01" の行 で集計対象となる 'カレンダー'[日付] は 2017-01-01 から 2017-01-31 の範囲となり、メジャー [受注額] はこの範囲で評価が行われる。次の行以降でも同様に 行ごと に評価されるので、月ごとの集計表となる。

[受注額]を月ごと累計したいので、行ごとに適用される範囲は置き換える必要がある。行ごとで適用されている範囲を置き換えるには CALCULATE 関数 を使用する。


メジャー:受注額CumulativeTotalInPeriod

受注額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] )
)

って表記したら少し区別つくのかな。機会があればそのうち整理。


できあがり

2017-12-13_14-52-31.png

デモファイル はご自由にどうぞ。


その他