I am trying to get a percentage calculation in my crosstab and cannot figure out the formula. I have Products and then a Yes/No variable nested on the rows. I have Dates going across and I have Quantity and Percent in the columns. Here is what I want:
JAN % FEB % MAR
----- ----- -----
Product 1 Qty YES 120 60% 150 75% 160 80%
NO 80 40% 50 25% 40 20%
Product 2 Qty YES 100 71% 110 65% 150 79%
NO 40 29% 60 35% 40 21%
Totals YES 220 65% 260 70% 310 79%
NO 120 35% 110 30% 80 21%
I am using the following Formula for the Percentage: percentage ( [Qty] at [YES/NO Flag] for [Month Date] ) but it is not calculating correctly at all. The results have the same percentages repeated throughout the crosstab. See attachment.
I have set the solve order for the Percentage to 3 and changed the Aggregate and Rollup to 'Calculated' to no avail. This is a Relational source, not a Cube-base sourced report. Any ideas?