公開日 2023年7月22日 最終更新日 2023年7月23日
目次
はじめに
データ出典
政府統計の総合窓口(e-Stat)（https://www.e-stat.go.jp/）
「就業j構造基本調査」（総務省）を基にデータ製作所が作成
もとデータの内容
下位階層＝「男」、「女」
上位階層＝「総数」
このような階層構造になっています。
通常は、
下位階層の値の合計＝上位階層の値
この方程式が成立します。
ところが、画像のように
下位階層の合計＝男＋女＝110,976,800
データの総数の値＝110,976,700
100の差異があります。
総数 (上位階層) だけを知りたいのであればもとデータの「総数」の値だけを使用すればよいのだから話は簡単です。男女の数 (下位階層) だけを知りたい場合も「男」「女」だけの値を使用すれば問題はありません。
そもそも、「下位階層の値の合計＝上位階層の値」これが成立していれば「総数」という上位階層はデータに必要はありません。
では、「下位階層の値の合計＝上位階層の値」これが成立しないとき、男女比を算出したいときにどうするのか？
方法１
下位階層の合計値を分母にする。男女比の合計値は必ず1になります。
方法２
総数の値を分母にする。男女比の合計値は1にならない。1との差分は「不明」にする。画像のデータのように男女比の合計値が1を超えるときはどうする？
方法３
もとデータへ「不明」というディメンションを設定して、下位階層の合計が総数の値と一致するようにする。
方法３は画像のように下位階層の合計値のほうが総数の値よりも大きくなるので「不明」の値がマイナスになります。これはちょっとおかしい。
また、横計の下位階層の合計値も総数と一致しないので、いったいどこに「不明」の値を追加すれば最終的に総計と一致するのかわからない。
ということで、方法３は却下。
結論として、方法１、方法２のどちらでも計算できる方法を紹介します。
使用するデータ
コピーしてテキストファイルへ貼り付けてください。投稿のとおり再現できます。
日付,性別,人数 (人) 2017/10/01,総数,110976700 2017/10/01,男性,53542900 2017/10/01,女性,57433900
方法１ 下位階層の合計を分母にする
ピボットテーブルとピボットグラフを作成
画像のようにピボットテーブルとピボットグラフを作成します。
値＝「人数 (人)」
凡例＝「性別」
「性別」のスライサーを表示します。
計算の種類
・値フィールドの「合計 / 人数 (人)」をクリック
・「値フィールドの設定」を選択します。
開いた窓の
「名前の指定」へ計算式名をタイプします。
・「計算の種類」タブ
・「計算の種類」のドロップダウンのなかから「行集計に対する比率」を選択します。
・「OK」です。
「男性」＋「女性」＋「総数」を分母にした比を計算できます。
スライサーで「総数」を除外します。
「男性」＋「女性」を分母にした比の計算になります。総計は1です。
方法２ 総数を分母にする
計算の種類
・値フィールドの「合計 / 人数 (人)」をクリック
・「値フィールドの設定」を選択します。
開いた窓の
「名前の指定」へ計算式名をタイプします。
・「計算の種類」タブ
・「計算の種類」のドロップダウンのなかから「基準値に対する比率」を選択します。
・「基準フィールド」は「性別」
・基準アイテムは「総数」
・「OK」です。
「総数」を分母にした計算になります。
男女比の合計は1になりません。
スラ―サーの「総数」を除外します。
何も表示されません。
基準値である「総数」が計算の分母ですから、これをフィルター除外すると計算ができないわけです。
DAX計算
フィルター関数を使用したDAX計算をすれば、フィルター除外した値を使用た計算が可能になります。
「総数」がピボットテーブルにもピボットグラフにも出現しないのに計算式に使用できます。
・テーブル名を右クリック
・「メジャーの追加」を選択します。
開いた窓の
・「メジャーの名前」は名称をタイプ
・数式へ次のテキストをコピペします。
=SUM('テーブル_data'[人数 (人)])
/
SUMX(CALCULATETABLE('テーブル_data','テーブル_data'[性別]="総数"),[人数 (人)])（注） 数式内にある３カ所の「’テーブル_data’」の部分はテーブル名ですので各自で変更してください。
「DAX式を確認」をクリックして式にエラーがないことを確認します。
「OK」です。
値フィールドへ作成したメジャーをドロップします。
スライサーの「総数」を除外します。
男女比の合計が1にならないピボットテーブルとピボットグラフが表示できていれば成功です。
