如何在Power BI里求复合增长率？

引子

PBI里有各种时间函数，网上关于同比、环比增长率的文章多如牛毛。复合增长率这个实操中也非常重要的指标，却提得比较少。本文跟大家一起探讨PBI里如何求复合增长率。

复合增长率——更合理地衡量多时间跨度的平均增长率

以下图这个极端的情况为例。第一年到第三年的年平均增长率是多少？

由于期末（第三年）和期初（第一年）均为100，所以，这两年综合增长率为0。这意味着，平均增长率不是常规的计算方式[100%+(-50%)]/2=25%。这种将N个值加总再除以N所得的平均数，叫做算术平均数，适用于求解在同一时间截面上，不同分类的平均值。不适用于求解在时间纵向上的平均值。

在时间纵向上，因为有指数的效应，所以需要用几何平均数，即N个值相乘再开N次方。上述例子中，这两年变化倍数的几何平均数=(200%*50%)^(1/2)=100%。即这段时间期末起初没有变化。

复合增长率，就是在变化倍数的几何平均数基础上-1。即：

复合增长率=(x1*x2*x3*...*xn)^(1/n)-1

其中，x为当年/上年，n为年数。由于x1*x2*x3*...*xn=期末指标/期初指标，所以复合增长率又可这么计算：

复合增长率=(期末/期初)^(1/n)-1

PBI里怎么求

假设我们的数据模型结构如下所示：

其中，数据以天为单位。根据上一节的定义，月度复合增长率的求法如下：

平均成本复合增长率 = 
var beginning_value=calculate([均成本],FILTER('完工成本',(year('完工成本'[完工月份])*100+(month('完工成本'[完工月份]))=MIN('Dates'[YearMonth])))) //其中均成本为度量值，另外做
var ending_value=calculate([均成本],filter('完工成本',(year('完工成本'[完工月份])*100+(month('完工成本'[完工月份]))=max('Dates'[YearMonth]))))
var duration=(max('Dates'[Year])-min('dates'[year]))*12+(max('dates'[month])-min('dates'[month])) // 考虑了跨年的问题
return 
    if(
        divide(ending_value,beginning_value)^divide(1,duration,1)=0,  //当时间切片器只选择了单一时间点，返回0
        0,
        divide(ending_value,beginning_value)^divide(1,duration,1)-1  

如果求年度复合增长率（CAGR），将duration改为max('Dates'[Year])-min('dates'[year])即可。上述求法综合考虑了年、月切片器筛选的问题，因此相对复杂和完整。

特殊情况

当数据的颗粒度与所求复合增长率颗粒度一致时，即假设表格每行代表年度（或月度）数据，目标求年度（或月度）复合增长率，则可用以下方法：

复合增长率=geomeanx(表，变化倍数)/100-1

geomean即求该时间段内的几何平均值。其中，变化倍数需另外建一个计算列=本期/上期。

不足

从公式可见，复合增长率只考虑期初期末，更适合于反映单调递增或递减的指标变化。而对于非单调性变化的指标，无法反映期间的发生了多少波澜。正好比当年关于NBA的一个笑话：那么多年没看NBA，詹皇还是在骑士，加内特还是在森林狼，谁知道中间发生了多少个决定1、决定2呢？