Power BIのDAX関数でCAGRを計算する方法
Power BIでCAGR（年平均成長率）を計算する方法です。CAGRは例えば自社の売上金額や販売個数の成長率を業界平均と比較したりする場合なんかに使いますかね。
当サイトのPower BIテンプレートで紹介している自動車業界の売上高分析でも使っています。
さて早速CAGRの計算式に関してですが、基本的にはExcelなんかで計算する方法と同様で、CAGRの計算式をそのままDAX関数に書けば計算できます。
CAGRは計算式の中でベキ乗（^）を使うのですが、こちらの記号もそのままPower BIで使うことができます。
CAGRの計算式
まずはCAGRの計算式ですが、下記の通りとなります。
(N年度の売上/初年度の売上)^{1/(N年-初年)}-1
この式に実際に数字を当てはめて計算してみます。
今回は下記のように2010年~2019年の売上高のCAGRを計算するというケースで考えてみたいと思います。
2010年：189,509億円
2011年：189,936億円
…
2019年：302,256億円
Power BIでのデータの持ち方としてはこんな感じです。
こちらのデータでは初年度は2010年の189,509億円となり、n年度は2019年の302,256億円ということになりますので、計算式に当てはめるとこうなります。
（302,256億円/189,509億円)^{1/(2019年-2010年)}-1
≒5.32です。
この計算式をPower BI上でDAX関数を使って表現してみようと思います。
DAX関数の書き方
DAX関数の方ですが、最終的にはに下記の通り持っていきたいです。
CAGR = ([n年度売上高]/[初年度売上高])^(1/[年数])-1
これは前の「CAGRの計算式」で書いた式と同じですね。
ただこの関数を導く過程で、1.n年度売上高、2.初年度売上高、3.年数に関しては、別途関数を用意しなければなりません。
この3つの関数の書き方は、データの持ち方等で人それぞれ異なるのですが、参考までに私が使った関数を紹介します。
1.n年度売上高
n年度売上高 =
CALCULATE(SUM(‘Automotive Revenue'[売上高]),FILTER(‘Automotive Revenue’,’Automotive Revenue'[年度]=MAX(‘Automotive Revenue'[年度])))
→Calculate関数のFilterでMAX（※2010~2019の中で最大の値を返す）でデータを絞り込み、最大の年数（この場合2019年）を返して合計しています。
2.初年度売上高
初年度売上高 =
CALCULATE(SUM(‘Automotive Revenue'[売上高]),FILTER(‘Automotive Revenue’,’Automotive Revenue'[年度]=MIN(‘Automotive Revenue'[年度])))
→初年度に関してもn年度と考え方は同じで、Calculate関数のFilterでMIN（※2010~2019の中で最小の値を返す）で絞り、最小の年数（この場合2010年）合計値を返す関数です。
3.年数
年数 =
(MAX(‘Automotive Revenue'[年度])-MIN(‘Automotive Revenue'[年度]))
最大の年数（この場合2019年）から最小の年数（この場合2010年）を引き算しています。
こんな感じでCAGRの計算式を分解し、それら1つ1つを別途DAX関数で式として作り、その式を組み合わせることで表現しています。
ポイントは最大の年数を返せるMAX（）と、最小の年数の値を返すMIN（）をCalculate関数のFilterとして利用するところですかね。
ちょっとわかりずらいかもしれないので、今度動画でも説明してみようかなと思います。
以上。
ソフ蔵
IT企業でデータ分析や分析基盤構築に携わってます。
主にPower BIの使い方やデータ分析のノウハウを伝えてます。
データを使って世の中やビジネスを可視化したり、データ駆動型の組織作りの支援をしています。
データ分析・可視化基盤の構築、データ活用方法、Power BIについて相談あれば下記からお問合せ下さい！！
Power BI・データ分析を学習しよう！！
Power BIレポートのテンプレートサイトはこちらです
Power BIの入門記事・記事をピックアップしましたので、こちらも参考にしてみて下さい。
▶Power BIとは？ 記事版 / 動画版（Power BIの概要を3分で解説！）
コメントを残す