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.
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 ...
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.
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.
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.
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.
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])
)
PS, I have tried taking out the "1+" form the LN() part of the calculation and still get the same error
Can you leave out the LN and just have the multiplier and this:
Cumulative Total – DAX Patterns
?
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.
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)
Like this thread? Share it with others