Excelのピボットテーブルでより自由な集計へ

Excelのピボットテーブルでは、単純な集計なら直観的にできます。
ただ複雑な集計となると、少し直感的でない操作をする必要があるので備忘録。

まずは単純な集計
例えば、以下の2015-2020年の県別人口から、

  

  
 ピボットテーブルで、県別最大人口を出すには、[挿入]メニューピボットテーブルで、
  

ピボットテーブルをつくり、

ドラッグアンドドロップで、「県名」を「行」の場所に配置し、「人口(千人)」を、「値」の場所に配置し、


「合計/人口(千人)」と表示された部分を左クリックし「値フィールドの設定」から、合計を最大に切り替える。


  
で基本的な集計はできた。
  
  
SQL文で疑似的に書くと以下のような感じ。
SELECT 県名,MAX(人口(千人)) FROM 県別人口 GROUP BY 県名

で本題。以下のような100万人を超えた県には"〇"そうでない県には"×"のようなものを書きたい場合。(SQLでは以下のイメージ)

SELECT 県名,CASE WHEN MAX(人口(千人)) > 1000 THEN '〇' ELSE '×' END FROM 県別人口 GROUP BY 県名

どうするかというと、OFFICE2016以降であればDAXでメジャー式が使える、という記事です。


まず、ピボットテーブルを挿入するときに出てくる以下のダイアログで、「このデータをデータモデルに追加する」にチェックを入れておくと、


列をまとめたテーブル名が表示されるようになる



このテーブル名を右クリックし、[メジャーの追加]を選ぶと、集計用の式が書けるようになる。


  


適当にメジャーを作ってみて、



OKを押すと、メジャーが表示されるようになる。


これを値の部分にドラッグアンドドロップすると、IF条件で表示を〇×に切り替えることができる。