Power BI Desktop で レポートを作成していくとき、モデリング機能で メジャー(Measure) を定義してビジュアルなどに使用します。メジャーって必要なのかと聞かれれば、"必須" と答える 。だって、メジャーがなければ単純な集計しかできないから。
メジャー とは
集計結果としたい値を算出するために 繰り返し利用できる "定義" という感じ。
メジャー と 計算列
メジャーも計算列も同じく計算や集計するものだけど、
- メジャーは、ビジュアルなどに表示されるときなど実際に評価されるまで計算は行われない。
- 計算列は、計算済みの値がテーブルの列としてデータモデル内に保存される。
ここが大きく違うポイントで、本来の目的も異なると考えてよいでしょう。
| メジャー | 計算列 | |
|---|---|---|
| 主な用途 | 集計する値 | 集計に用いる値の列 |
| 見出し 軸 凡例に | 使用できない | 使用できる |
| 計算されるとき | メジャーを参照したとき | データモデルを更新したとき |
| レポート フィルターなどに | 使用できない *1 | 使用できる |
*1 ビジュアル レベル フィルターには使用できる。
定義しなくてもメジャーは使用されていると考える
メジャーを定義しなくても、Power BI の中の人は 列(Column) の値を集計しビジュアルなどに結果を表示する。Power BI のドキュメントではあまり使われていないようだけど、暗黙のメジャー(Implicit measures)という表現できる機能があるのです。既定では、合計 もしくは カウント が使用され、平均 など他の集計方法に変更することができる。列や行ごとで比較集計もできるようになっている。このあたりの機能は Excel ピボット テーブル と同じと考えてよい。
どのような計算/集計をするのかを明示
"列"を集計したいアイテムとして使用したとき、ひとまずの集計をしてくれるのは便利ではあるけれども、レポートを作成しているとき、修正しているとき、ユーザに公開したとき、いちいち集計方法を設定しなければならないのはとても面倒だし使いにくい。なので メジャーは必ず定義し使用するという方針が望ましいと思う。
"テーブル" に [金額] 列があり、その和を "合計金額" として集計するならば、
合計金額 = SUM( 'テーブル'[金額] )
とすればよい。計算/集計された結果は SUM 関数 により必ず [金額]列の和になるから。
また、どのような結果を表しているのか明確になるので メジャーとして定義し意味ある命名をすることも重要。
メジャーを変数のように利用
定義したメジャーは 別のメジャーで変数のように利用できる。
予算額 = SUM( '予算テーブル'[金額] )
実績額 = SUM( '実績テーブル'[金額] )
というメジャーが定義されているとき。その対比を計算するメジャーは
消化率 = DIVIDE( [実績額], [予算額] )
という定義にすることが可能で記述がシンプルになる。
その計算列は必要?
金額 = [単価] * [数量]
という定義で "金額" という計算列を Excel のようにテーブルに追加できるのですよ‼
合計金額 = SUM( [金額] )
というメジャーを作成するとビジュアルに使えますよ！というデモをすることがある。
Power BI Desktop のモデリング機能を説明するときには必要なのだけど、この計算列は必須ではない。
で、どうするかといえば、
合計金額 = SUMX( 'テーブル1', [単価] * [数量] )
というメジャーを定義します。イテレータと分類してよいと思うのですが "X" がつく関数は、メジャーを定義していくうえで非常に重要。そのうち取り上げたいと思うけど、まず少しだけ。
SUMX(
<table>, // テーブル名 もしくは テーブルを返す式
<expression> // 引数 table の行ごとに計算される式
)
第1引数 table の 行ごとに 第2引数 expression が評価され、その結果を加算していくのが SUMX 関数。どちらの引数でも 式 を利用できるので、
SUMX(
FILTER(
'テーブル',
[数量] > 0
),
SWITCH(
TRUE()
, [単価] >= 1000, [単価] * [数量] * .03
, [単価] >= 500, [単価] * [数量] * .05
, 100
)
)
というロジックをメジャーに適用することができるようになる。例にあるロジックには特段の意味はないけれども、集計に利用するテーブルを 式 で用意し、そのテーブルの列から和を求めるということができる。
メジャーとフィルター
メジャーで得られる結果はフィルターに影響を受けるということなのだけど、マトリクスを例にして整理しておく。
自動的に適用されるフィルター
| 年 | 区分 | 数値 |
|---|---|---|
| 2017 | A | 20 |
| 2017 | B | 25 |
| 2018 | A | 30 |
| 2018 | B | 35 |
| 2019 | A | 10 |
| というテーブルがあり、[数値]列を集計するメジャーを |
Total = SUM( 'テーブル'[数値] )
とする。このメジャーを使用し [年]と[区分]のマトリクスを作成するとこうなる。
| 年\区分 | A | B | 合計 |
|---|---|---|---|
| 2017 | 20 | 25 | 45 |
| 2018 | 30 | 35 | 65 |
| 2019 | 10 | 10 | |
| 合計 | 60 | 60 | 120 |
ここで理解しておくことは、マトリクスの見出し(列見出しと行見出し)以外は定義したメジャーによって計算された値ということ。そして、マトリクス各フィールドの値として結果を得るとき自動的にフィルターが適用されている。
どのようなフィルターが適用されるのか
メジャーは実際の計算集計を行うときフィルターの影響を受けるのだけど、多くの場合 各ビジュアルの見出しや軸に沿って集計されるようフィルターが自動的に適用される。
| 年\区分 | A | B | 合計 |
|---|---|---|---|
| 2017 | [年] = 2017 && [区分] = "A" | [年] = 2017 && [区分] = "B" | [年] = 2017 |
| 2018 | [年] = 2018 && [区分] = "A" | [年] = 2018 && [区分] = "B" | [年] = 2018 |
| 2019 | [年] = 2019 && [区分] = "A" | [年] = 2019 && [区分] = "B" | [年] = 2019 |
| 合計 | [区分] = "A" | [区分] = "B" | (すべて) |
Total_Filterd = IF( ISFILTERED( 'テーブル'[区分] ), [Total] )
という感じで確かめるとフィルターの適用されている感じがよくわかる。
フィルターの置き換えを定義
定義したメジャーをビジュアルに使用するとき、自動的に適用されるフィルターを制御しなければならないことがある。たとえば、全体と比較した割合とか。これらはメジャーで明示的に定義すればよい。
年ごとで全体との割合を計算するには、
- 同年での全体の和が必要
- 同年全体の和は [区分]列に適用されたフィルターを解除すればよい
割合(年) = DIVIDE( [Total], CALCLATE( [Total], ALL( 'テーブル'[区分] ) ) )
ALL( 'テーブル'[区分] )
[区分]列に対するフィルターを解除
CALCLATE( [Total], ALL( 'テーブル'[区分] ) )
自動的に適用されるフィルターから[区分]列に対するフィルターを解除し、[Total] を計算