当Power BI遇上数学:用DAX解决数据的循环迭代

本文来自PowerBI星球嘉宾Davis的分享,Davis曾在知乎上发表过数篇高质量的PowerBI文章,是不折不扣的PowerBI技术大神。当我邀请他为星友们也分享一些干货时,他欣然同意,于是有了这篇精彩的文章,下面就请大家慢慢欣赏吧。


用DAX解决数据的循环迭代

Davis(知乎@九州殊口增七)

一、循环迭代的难处

实际业务中,数据迭代应用广泛,如在复利现值、折旧等方面的计算,然而众所周知的是,DAX是一个基于列引擎的函数语言,数据中每行基于其各自行上下文计算。

但我们需要的效果是,列的第二行的数据基于第一行数值的计算产生,以此类推,列的第n行数据基于其第n-1行求得,这样的操作在Excel可以很轻松求得(如下图中的A、B、C三列),

但在Power BI(或PowerPivot)中,你虽然可以基于某个列利用诸如SUMX等函数进行迭代运算(如图2所示的两种方法),

但却难以基于某列的第一行的值,迭代该列自身而求出该列,换句话说,你可以很轻松依据A列的筛选上下文利用SUMX等类似的函数在B列求出其迭代结果(或在自定义的度量值得出结果)。

但却难以实现当某列只有第一行有数值时,如何通过迭代其自身得出其第2至第n行的值,这样的计算在Excel很轻松,但用DAX却不易实现。

二、迭代的本质是数列

事实上,DAX同样可以完美解决该问题,因为正如本段标题所言:迭代的本质是数列。当你用数列的思维去思考在Power BI的迭代问题,就能简单多了。

因此,针对于上图A列的情况:当列的n+1行等于第n行加d时,整列的数据其实就是一个公差为2的等差数列,如下,我们只需要利用等差数列公式即可:

同理,如果情况如同B列:n+1 = n * 2, 那么这就是一个等比数列:

三、如何解决加减乘除类型的混合迭代

正如图1的C列。当迭代公式类如“f(n +1) = (f(n) + d)*q”时,其逻辑就不像上面公式简单了,但实际上,这是一个数学问题了,本文在此不做拓展。

其答案是:尽管此时数列f(n)本身是不规则数列,但f(n) – f(n-1)却是一个规则数列,而且是等比数列。

依据这个思路,我们的可以先利用等比数列规则求出f(n) – f(n-1),然后反推出f(n),实际上就是高中数学的差分法了。首先,f(n) – f(n-1)的计算公式为:

注1:以如图1的C列为例,其公差为3,公比为2,则f(n) – f(n-1)为首项为f(2)-f(1),公比为2的等比数列;

注2:第14、15行处,由于差分法会使第一项留空,故使用IF(ISBLANK(),,)使第一行有值。

在得出f(n) – f(n-1)后,即可反推出f(n)的值,因为f(n)是一个以f(n) – f(n-1)为公差的不等差数列,计算如下:

这样我们就成功完成了在这种复杂公式计算下的循环迭代。经测试,无论我们在公式中如何改变D和Q的值,该公式都返回了正确的循环迭代结果。


感谢Davis的精彩分享,大家以后再碰到数据迭代相关的难题,都可以借鉴本文的思路。星友们遇到问题时,也可以在知识星球中随时向Davis提问哦。

(0)

相关推荐