5 Replies Latest reply: Nov 13, 2012 4:10 PM by Jason Michaelides

# Use value from another row in calculation

I have a straight table with two dimensions, Line Number and Line Description, the expresssion sums Balance.

I want to create a second expression that uses the Balance sum from the row where Line Description = 'Net Sales' to calculate a percent value.  So, on the row where Line Description = Product Sales it would look something like SUM(Balance) / [SUM(Balance) from Net Sales].  I can't use Above or Below as the number of rows is not static.

Any ideas?  Thanks in advance for any help.

• ###### Re: Use value from another row in calculation

Hi. You may use Set Analysis.

SUM(Balance) / [SUM({<[Line Description]={'Net Sales'}>} Balance)]

Regards.

• ###### Re: Use value from another row in calculation

Appreciate the quick reply!  This doesn't work as it calculates the second part as 0 except where the dimension matches the Set Analysis.  So, if I only do the [SUM({<[Line Description]={'Net Sales'}>} Balance)] part all values come up as zero except the row where the Line Desc is actual Net Sales.  I am assuming Set Analysis would still honor the dimensions so it cannot do this calculation.  Thanks though.

• ###### Re: Use value from another row in calculation

Try using the TOTAL qualifier along with the set analysis:

SUM(Balance) / [SUM({<[Line Description]={'Net Sales'}>} TOTAL Balance)]

That should disregard the dimensions.

Hope this helps,

Jason

• ###### Re: Use value from another row in calculation

Jason,

You are my favorite person today!  Thanks so much!

Kindest regards,

Heather

• ###### Re: Use value from another row in calculation

You're very welcome :-)