I am trying to define a computed measure in FM model to calculate the percentage change between
two columns and this is the expression I am using
(total(column A) - total (column B)) / total (column B)
This expression works correctly at the detail level(lowest) of the dimension hierarchy but
when queried at the summary level it produces a wrong result (like percentage computed
using the total for the entire report).
FYI: I tried defining the same formula in a report studio report and it just works fine,
however it seems to have problem when the same expression is defined as a column
in FM model (It ignores the groupings alltogether and uses the report total).
I had a similar situation. In FM I created a folder called calulations and created the calculations I needed using the QS fields but not in the QS itself.
I have tried this solution without any joy and still get the original issue that ganesansgi was getting.
ganesansgi did you mange to solve the issue?
If so would you mind sharing?
Many thanks
sbss
As a matter of rule, I always create percentage calculations in the tool that is being used to view the results (Report Studio, Query Studio, Cognos Connection, Upfront etc). That way, the calculation is performed at run time so it calculates based on whatever is displayed. Hence, percentage for summary is calculated rather than aggregated.
The tool that builds something (Transformer, Framework Manager) deals with data at the lowest level of granularity so the percentage should not be done there as it will roll it up which will be wrong!!