Product of Previous Value

chaserracer83

New Member
Joined
Oct 8, 2012
Messages
27
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...

MultiplierValueFinal Value
2%100102
3%102*3%
4%102*3%*4%
5%102*3%*4%*5%

<tbody>
</tbody>

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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.
 
Upvote 0
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:

AgeMultiplierValueFinal Value
11%50100
21%50100
32%50100
42%100102
53%50102*3%
64%50102*3%*4%
75%50102*3%*4%*5%

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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.
 
Upvote 0
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:

AgeMultiplierValuePre-Calculation
11%50
21%50
32%50
42%100102%
53%503%
64%504%
75%505%

<tbody>
</tbody>

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.
 
Upvote 0
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])
)


 
Upvote 0

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\CABCDFormula for D Column
000B - A = 000
1100200B - A = 100100=IF(D0+C1<0,0,D0+C1)
2700B - A = -7030=IF(D1+C2<0,0,D1+C2)
31000B - A = -1000=IF(D2+C3<0,0,D2+C3)
4100300B - A = 200200=IF(D3+C4<0,0,D3+C4)

<tbody>
</tbody>

Can you help me solve my case?

Thanks,
Budy from Indonesia
 
Last edited:
Upvote 0
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-bi/942591-using-multiple-criteria-sum-column-same-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)
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top