Given the First Two Columns at Left, How Do We Calculate the Two Columns at Right?
(It’s 1.14 * 1.18 * 1.08 * 1.08… How do we do that?)
I Take No Credit for the Question Or the Answer!
So much coolness and none of it is mine. Today I am merely a messenger.
First of all a great question came in on an old blog post (the one about Moore’s Law and Inflation):
HOW do you perform cumulative inflation calculations in Powerpivot. What seems trivial, multiplying all the values in a column together has me stuck, as there is no PRODUCT() formula. The only way i can think of doing it is through messy iterations but that can’t be it.
Awesome question Andy! And I was stumped. How do we multiply every number in a column together, which is what we need to do here? I mean, if we had a PRODUCTX() function I’d know where to begin.
To the Mother Ship (Microsoft) We Go!
I think the world needs more X functions (I’m serious, that’s not a joke, although it IS a funny thing to say, so I say it often).
So I took this opportunity to ask my former colleagues at Microsoft – can we have a PRODUCTX function in the future? And is there a way to “fake it” in the meantime? Spoiler alert: the answer is yes.
Jeffrey Wang is One Smart Dude
Jeffrey works on the DAX development team at Microsoft. He also blogs occasionally, and the topics he covers over there generally go over my head.
He responded to my mail and said:
If X = A1 * A2 * … * An
Ln(X) = Ln(A1) + Ln(A2) + … + Ln(An)
Now you have turned product into sum.
x = Exp(Ln(x)) = Exp(Ln(A1) + Ln(A2) + … + Ln(An))
Don’t worry if you don’t understand that. All he’s really saying is “if you find yourself wanting a PRODUCTX function, you can use a combination of EXP, SUMX, and LN to get the same result.”
Which is mathemagical.
Aside: Donald Duck in Mathmagic Land!
That reminded me of an old Disney movie that they used to show in elementary school. I mean, it was shown to us on film projectors. And it was amazing. Still is! And now available on youtube!
Donald Duck Would Approve of Jeffrey’s “Mathemagical” Conversion of PRODUCTX into SUMX
(Click here to watch the full 27-minute movie)
(Hmmm… Is there anyone out there who can do a top-notch Donald Duck impression? If so, drop me a note – I’ve got an idea. I’m Rob. At P3 Adaptive . Dot com.)
Putting Jeffrey’s Trick to Work in a Calc Column
Here we go:
[Cumulative Multiple] =
=EXP(
SUMX(
FILTER(Rates, Rates[Year]<=EARLIER(Rates[Year])),
LN(1 + Rates[Rate])
)
)
And the explanation:
Yellow – we want to sum up all the natural logs (LN) of 1 + each rate value. Again, because Jeffrey says so.
Green – but we only want to “count” years that have already happened. (Note that you can change the <= to a pure < if you don’t want to count the current year. Sometimes you will want to count the current year and sometimes you won’t).
Grey – after we’ve summed up all the LN(1 + Rates[Rate]) values via SUMX, we then raise “e” to the power of that sum via the EXP() function. Again, because Jeffrey says so.
Does it work? You bet:
1.569 Times Original Amount = 156.9% = 56.9% Growth
(Subtract 1 From the Original Result to See the Cumulative % Change)
Now as a Measure!
Same “mathmagic” principle, just different execution:
=EXP(
SUMX(
FILTER(ALL(Rates), Rates[Year]<=MAX(Rates[Year])),
LN(1 + Rates[Rate])
)
)
The highlighted section is the only difference. And it, too, works:
Now Available in a Measure Near You!
Wait, How is This Different From Viral Growth and Condoms Power???
Yeah, one of the more novel posts and then one of the flat-out most humorous post titles ever.
Neither of those posts involves EXP(SUMX(… LN(…))) because in both of those posts, the growth rate is constant rather than variable. If we had the same interest rate (or inflation rate) every single year, we could have used those (arguably simpler) techniques instead.
