Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to create a pivot table where the columns are a ratio of the current column value to the previous column value. For example, each column is a cumulative total of all the columns before...so the first value is 200,000, the second value is 150,000, the third value is 250,000. So, Column 1 would be 200,000, Column 2 would be 350,000 (200,000 + 150,000) and Column 3 would be 600,000 (200,000 + 150,000 + 250,000). Then the vaues in the pivot table would need to be : Column 1 would be the ratio of Column 2/1 (350,000 / 200,000), Column 2 would be the ratio of column 2/3 (600,000 / 350,000) so on and so forth. I cannot for the life of me figure this out. FYI, the sums are calculated as a RangeSum of the current column and the previous column so that I get a cumulative total all the way across. I tried simply dividing the column calculation by BEFORE(column expression) but that did not work.
As far as I know you need to use actual expressions and cannot use Before.
So your column measures would be :
1 = Column2_RangeSumMeasure / Column1_RangeSumMeasure
2 = Column2_RangeSumMeasure / Column3_RangeSumMeasure