Results 1 to 10 of 10

Product of Previous Value

This is a discussion on Product of Previous Value within the Power BI forums, part of the Question Forums category; Good morning, I am working in PowerPivot and need a column that take the value from the previous row and ...

  1. #1
    New Member
    Join Date
    Oct 2012
    Posts
    27

    Default Product of Previous Value


    Good morning, I am working in PowerPivot and need a column that take the value from the previous row and multiplies it by the value of another column in the same row...

    Multiplier Value Final Value
    2% 100 102
    3% 102*3%
    4% 102*3%*4%
    5% 102*3%*4%*5%

    I am lost about how to calculate the "Final Value" column. Any help would be greatly appreciated.

    Thank you,
    Chase

    P.S. My company only has PowerPivot installed, so I have to accomplish this exclusively in PowerPivot without using the other Power BI tools.

  2. #2
    Board Regular 63falcondude's Avatar
    Join Date
    Jan 2016
    Posts
    3,468

    Default Re: Product of Previous Value

    I don't know the first thing about Power Pivot (Excel 2010 here) but maybe this will help anyway.

    In C3 (of your example) =C2*A3 then drag down.

  3. #3
    New Member
    Join Date
    Oct 2012
    Posts
    27

    Default Re: Product of Previous Value

    63Falcondude, thank you for your reply. PowerPivot doesn't use cell references which is why I am having trouble figuring out how to accomplish this.

    EDIT: My original post excluded some of the complications. I was afraid I might be given a good solution and not know how to add the filters, so the edit below includes my full problem...

    Good morning, I am working in PowerPivot and need a column that takes the value from the previous row and multiplies it by the value of another column in the same row. It gets a little more complicated than that; the default value is 100 until "Age" = 4, at "Age" = 4 you take the "Value" within the same row multiplied by the "Multiplier", after "Age" = 4 you take the previous value and multiply by the "Multiplier". Example below:

    Age Multiplier Value Final Value
    1 1% 50 100
    2 1% 50 100
    3 2% 50 100
    4 2% 100 102
    5 3% 50 102*3%
    6 4% 50 102*3%*4%
    7 5% 50 102*3%*4%*5%

    I am lost about how to calculate the "Final Value" column. Any help would be greatly appreciated.

    Thank you,
    Chase

    P.S. My company only has PowerPivot installed, so I have to accomplish this exclusively in PowerPivot without using the other Power BI tools.

  4. #4
    New Member
    Join Date
    Oct 2012
    Posts
    27

    Angry Re: Product of Previous Value

    Turns out 2016 has a ProductX function, but I am working in 2013. I found an external thread that suggests using EXP() and LN()...

    I created a pre-calculation column that has the values I want the products so my data currently looks like this:

    Age Multiplier Value Pre-Calculation
    1 1% 50
    2 1% 50
    3 2% 50
    4 2% 100 102%
    5 3% 50 3%
    6 4% 50 4%
    7 5% 50 5%

    The pre-calculation field is working how I would like for it to, but I am getting a "calculation" error in the final calculation. My final calculation looks like this:
    =EXP(
    SUMX(
    FILTER(Table,
    Table[Age]>=4
    && Table[Age]<=Earlier(Table[Age])
    ),
    LN(1+Table[Pre-Calculation])
    )
    )

    I am desperate for help on this. Thank you to anyone that can help me sort this out.

  5. #5
    Board Regular
    Join Date
    Sep 2005
    Posts
    4,049

  6. #6
    New Member
    Join Date
    Oct 2012
    Posts
    27

    Default Re: Product of Previous Value

    Well... that suggestion is similar to the one I found, but they setup the formula slightly different. I tried this way and I am still getting this same error:

    Calculation error in column 'Table'[]: An argument of function 'LN' has the wrong data type or the result is too large or too small.

    This is my new equation based on the link:

    =CALCULATE(
    SUMX(Table, LN(1 + Table[Pre-Calculation])),
    FILTER(Table,
    Table[Age]>=4
    && Table[Age]<=Earlier(Table[Age])
    )



  7. #7
    New Member
    Join Date
    Oct 2012
    Posts
    27

    Default Re: Product of Previous Value

    PS, I have tried taking out the "1+" form the LN() part of the calculation and still get the same error

  8. #8
    Board Regular
    Join Date
    Sep 2005
    Posts
    4,049

    Default Re: Product of Previous Value

    Can you leave out the LN and just have the multiplier and this:

    Cumulative Total – DAX Patterns


    ?

  9. #9
    New Member
    Join Date
    Mar 2014
    Posts
    29

    Default Re: Product of Previous Value

    Quote Originally Posted by sheetspread View Post
    Hi,

    I have a similar problem actually, I wanna do a recursive calculation in powerpivot but the calculation little bit different.
    Well actually I have posted my case in another thread.
    The data is like this:

    R\C A B C D Formula for D Column
    0 0 0 B - A = 0 0 0
    1 100 200 B - A = 100 100 =IF(D0+C1<0,0,D0+C1)
    2 70 0 B - A = -70 30 =IF(D1+C2<0,0,D1+C2)
    3 100 0 B - A = -100 0 =IF(D2+C3<0,0,D2+C3)
    4 100 300 B - A = 200 200 =IF(D3+C4<0,0,D3+C4)

    Can you help me solve my case?

    Thanks,
    Budy from Indonesia
    Last edited by pociners; Jun 15th, 2016 at 03:18 AM.

  10. #10
    New Member
    Join Date
    Oct 2012
    Posts
    27

    Default Re: Product of Previous Value

    Hey there, I was able to solve my issue using the link sheetspread provided. I was getting some errors before, and honestly I have no idea what I changed, but it is working now.

    Pociners - Your issue is a little different because you can use calculate(sum()) with some filters to get your answer. I actually had the same issue and it was solved in this post http://www.mrexcel.com/forum/power-b...ame-table.html the same solution should work for you, just modify your adjusters so that the R/C numbers is less than the EARLIER(R/C)

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com