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として利用するところですかね。

ちょっとわかりずらいかもしれないので、今度動画でも説明してみようかなと思います。

以上。

本サイト運営：銀タカ

Power BIの使い方やデータ分析のノウハウを伝えております。
データを使って世の中やビジネスを可視化したり、データ分析基盤作りの支援をしています。
本業では小売企業でビッグデータの分析や分析基盤の構築に携わっています。

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

最前線の情報を網羅した「レポート・マクロ・データ」を提供しています。
重要な意思決定を支える、他にはない価値あるデータをぜひご活用ください！

Power BIレポート・マクロ・データ一覧を見る

Power BIレポートのショーケースも一部公開しております。

▶Power BIショーケースサイト

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

▶Power BIとは？　記事版　/ 動画版（Power BIの概要を3分で解説！）

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

 

Zou Data Platformもよろしくお願いします！

有料会員様向けに、以下の特典をご用意しております：

• データ：経済情報、緯度経度等のデータのご提供
• オンラインコース：専門的なスキルを学べる豊富なコース！
• レポート：実務で使えるレポートのダウンロード！更新版のデータもダウンロード可

会員様のDX化、データドリブンな会社運営をサポート致します。
ぜひ、この機会にご登録をご検討ください。

Zou Data Platformの詳細・登録はこちらから

お問合せはこちらから