Hi,
Can anyone please help on following issue on Cross tab report build on Cube (Not relational)?
We are trying to calculate percentage in Grand Total for following calculation, it give correct result on row level data (e.g. 24% , 68% etc..) but on Grand total it shows wrong values (e.g. -1483.8765934808%) , we tried almost all the ways values but not helping:
Syntax for calculation:
((Sales Value/ neutral) - Cost) / (Sales Value/ neutral)
Following are the original calculation based on above syntax:
( (total (
(total ([Sales Value] within set (periodsToDate ( [Sales].[Time].[Fiscal].[Year] , [SelectedMonth])))
/ total ([C_neutral] within set [Currency ID],[Selected Year]))
-
(total ( [Sales Cost - Base] within set (periodsToDate ( [Sales].[Time].[Fiscal].[Year] , [SelectedMonth])) ))) )
/
total ( total ([Sales Value] within set (periodsToDate ( [Sales].[Time].[Fiscal].[Year] , [SelectedMonth] )) ) / [C_neutral] within set [Currency ID],[Selected Year]))
Note:- We have also tried using "aggregate" function instead of" total"
Thanks & Regards, Amit Joshi
Have you tried setting the Solve Order of the Summary Row to a value greater than 1? Not sure if this will assist you, but it has helped me resolve issues with summary rows giving weird output in the past.
The link below is to a thread which expands upon Solve Order and how it functions.
Good luck!
http://www.cognoise.com/index.php?topic=21762.0 (http://www.cognoise.com/index.php?topic=21762.0)