Cumulative Interest or Inflation, Multiplying Every Value in a Column, Why Don’t We Have PRODUCTX?

 
Cumulative Inflation/Interest in Power Pivot - Who Needs PRODUCTX?

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!

image image

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 powerpivotpro.  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:

Cumulative Inflation/Interest in Power Pivot - Who Needs PRODUCTX?

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:

Cumulative Inflation/Interest in Power Pivot - Who Needs PRODUCTX?

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.

11 Responses to Cumulative Interest or Inflation, Multiplying Every Value in a Column, Why Don’t We Have PRODUCTX?

  1. Chu Xu says:

    Wow, well done Jeffery!

  2. Anne says:

    hm, found myself wondering…if I had a set of values with exchange rates e,g, Jan-13, 1000 ER 1.5, Feb-13,2000,ER 1.2 but needed to add them all together so that at the end of February I’d have (1500 + 1200 = 2700) but I needed to set up a measure that would do that…would this approach work for it I wonder? @PivotPro – think your Donald Duck example is quack good……

    • powerpivotpro says:

      Without understanding it too deeply, I’d guess your solution would look a lot like the measure formula at the end of this post, but without the EXP() “outer wrapper.” You’d also NOT need the LN() function of course and instead just be SUMX’ing the [Rate] column itself. And you’d need to adjust the FILTER to “fit” your particular calendar.

  3. ruve1k says:

    Rob, there was a thread on MrExcel.com that touched upon this topic on Feb 8th, 2012.
    http://www.mrexcel.com/forum/powerpivot-questions/611808-attempting-calculate-product-previous-values.html#post3033475

  4. powerpivotpro says:

    Dave and Reuvain – I’m not surprised that others have written about this before. But it was still new to me. And I like it :)

      • powerpivotpro says:

        Wow that’s funny! :) Doesn’t shock me. My brain recalls movie quotes from 20 years ago but can’t access the name of the person I spoke to yesterday. I probably look at 100 DAX “puzzles” a month, and not always with consistent levels of “engagement.”

        It’s pretty clear to me that I didn’t “engage” with that thread other than to say I wish we had PRODUCTX. Didn’t register with me that all of the logarithmic gobbledygook was a mathematical transformation of PRODUCTX into SUMX, or the part of my brain that DOES remember things would have picked it up :) I don’t even remember emailing MS about it back then, which I clearly DID do.

        Anyway, it was new to Rob Collie, November 2013 version, even though Rob Collie February 2012 had walked right past it without taking note. I’m still trying to get the Yesterday and Today versions to talk to each other.

  5. Stefan Zhelev says:

    Hi powerpivot pro,

    This approach for calculating the Total Product between the values of a column works in case there is another column – in your case year, which is used to order the values and multiply the value from each row with the value from the previous.

    This is a very isolated scenario. I need a solution that will produce a measure that calculates the multiplication of all column values, and when placed in a pivot table would result in the product of all members in the correct context.

    Here is an example table:

    DateKey GoodTiming Symbol Increase Factors
    20140406 No GIB -39 -38
    20130314 Yes GIB 0.17 1.17
    20130518 Yes GIB -0.05 0.95
    20120829 Yes DOX 0.21 1.21
    20090419 Yes INFY -0.41 0.59
    20120810 Yes INFY 0.11 1.11

    Here is an example pivot table:
    GoodTiming Yes

    Row Labels Product of Factors
    DOX 1.21
    GIB 1.1115
    INFY 1.7
    Grand Total 0.880784834

    Can you adapt your solution?
    Thanks!

Leave a Comment or Question