I have a crosstab with the following details:
ROWS: [Year] with [Product] as a child.
COLUMNS: Several fact types, called [FX Rate], [Forecast Percentage], [ML] and [PRM], are used to generate query calculations, which are the columns.
A sample calculation for a column would be [Converted PRM] = [PRM] * [FX Rate] * [Forecast Percentage].
However, I want this calculation done before the query sums up the values - at the moment, it's summing everything up for every product before performing the calculation, so sum(a)*sum(b)*sum(c) instead of sum(a*b*c).
I've tried setting aggregation to "None" on the non-calculated columns, and leaving it as "Total" on the calculated columns, but this doesn't seem to have worked - instead of rolling up for each product I get numbers across the top of the crosstab. I also tried setting rollup aggregation to "None" but this blanked the table.
Could anyone please explain how to get the calculation to be performed prior to the aggregation?
Thanks!
ROWS: [Year] with [Product] as a child.
COLUMNS: Several fact types, called [FX Rate], [Forecast Percentage], [ML] and [PRM], are used to generate query calculations, which are the columns.
A sample calculation for a column would be [Converted PRM] = [PRM] * [FX Rate] * [Forecast Percentage].
However, I want this calculation done before the query sums up the values - at the moment, it's summing everything up for every product before performing the calculation, so sum(a)*sum(b)*sum(c) instead of sum(a*b*c).
I've tried setting aggregation to "None" on the non-calculated columns, and leaving it as "Total" on the calculated columns, but this doesn't seem to have worked - instead of rolling up for each product I get numbers across the top of the crosstab. I also tried setting rollup aggregation to "None" but this blanked the table.
Could anyone please explain how to get the calculation to be performed prior to the aggregation?
Thanks!
