Power BIで複数列の平均値を計算する方法

Power BIにおいて、1列分のみの平均値の計算ならAverage関数を使えばいいのですが、複数行の平均値を計算した場合はどうすればいいのでしょうか。

 

例えば下図のデータのような場合です。

こちらはPISA(国際学力調査)が発表している国別の学力テストのスコア結果なのですが、

列:Reading_All(国語)・列:Mathematics_All(算数)・列:Science_All(理科)と科目別でスコアが分かれていて、

この3科目全ての平均スコアを計算したいといった場合です。

 

しかも、あろうことか、スペインのReading(国語)は、テストが実施されなかったからか?、空白です。

ちょっと厄介です。

 

ということで、下記手順で進めてみました。

 

※※データは下記リンクよりダウンロードいただけるので一緒にやってみましょう!※※

ファイル:PISA_Ranking_2018

 

Step1:カスタム列の追加

まずは、スペインの空白値の対応からです。

他の国は、3列の合計値÷3(科目)とすんなりいくのですが、スペインは2列の合計値÷2となり、計算方法が異なります。

 

ということで、カスタム列を新しく追加し、この「空白値」の個数をカウントします。

 

Powerクエリを開いて、「条件列」を選択します。

 

下図のように条件を作りました。

列の中に「null」がある場合、出力が1となる感じです。

 

そしてその他の2列分に関しても、同じように条件列を作ります。

合計3つのカスタム列が出来上がるというわけです。

 

Step2:メジャーの作成

メジャーは下記のように作りました。

 

Average =
VAR numerator =
CALCULATE(SUM(‘Sheet1′[Mathematics_All]) + SUM(Sheet1[Reading_All]) + SUM(Sheet1[Science_All]))
VAR denominator = 3 – ‘Sheet1′[カスタム.1] – ‘Sheet1′[カスタム.2] – ‘Sheet1′[カスタム]
Return DIVIDE(numerator, denominator)

 

・分子はCalculate関数で国語、算数、理科の3科目分の合計値を計算し変数numeratorに格納。

・分母は3科目から、空白値を減算した結果を変数denominatorに格納。

・Divide関数で、分子/分母といった感じです。

 

 

結果

結果はこんな感じ。

一応ゴリゴリだけど、計算は合ってます。

 

はい、ということで、もっと簡単な方法あればコメントで教えて下さいませ。

では、また。

 

※動画版はこちら

 

 

ソフ蔵

ソフ蔵

IT企業でデータ分析や分析基盤構築に携わってます。
主にPower BIの使い方やデータ分析のノウハウを伝えてます。
データを使って世の中やビジネスを可視化したり、データ駆動型の組織作りの支援をしています。

データ分析・可視化基盤の構築、データ活用方法、Power BIについて相談あれば下記からお問合せ下さい!!

Power BI・データ分析を学習しよう!!

Power BIレポートのテンプレートサイトはこちらです

Power BIショーケースサイト

ココナラ(Power BI)

Power BIの入門記事・記事をピックアップしましたので、こちらも参考にしてみて下さい

▶Power BIとは? 記事版 / 動画版(Power BIの概要を3分で解説!)

▶Power BIのメリット・デメリット 記事版 / 動画版

 

 

twitteryoutubemail

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

CAPTCHA


Scroll to Top