PowerBI

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

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

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

その他