Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Hi. You may use Set Analysis.
SUM(Balance) / [SUM({<[Line Description]={'Net Sales'}>} Balance)]
Regards.
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.
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
Jason,
You are my favorite person today! Thanks so much!
Kindest regards,
Heather
You're very welcome 🙂