I need to define a calculation in a crosstab intersection. For example, Actual, Budget, % of Budget are the 3 columns with Product Type and Camping Equipment nested underneath.
Actual Budget % of Budget
Tents 10 10 1
Sleeping Bag 5 10 .5
Camping Equipment 15 20 1.5 Should be 15/20 = .75
I do not want the intersection of % of Budget and Camping Equipment to aggregate. Instead I want to calculate (Intersection of Actual, Camping Equipment)/(Intersection of Budget, Camping Equipment).
value(tuple([Budget], [Camping equipment]))/ value(tuple([Budget], [Camping equipment]))
Using the calculation above as another column in the crosstab gets the correct value, but when I place the calculation in the intersection, nothing shows. I also set the properties for the calculation as 'Calculated' for the regular aggrgation and 'None' in the Rollup Aggregation.
What am I missing?
Set the solve order property for the % of Budget to a value 2
Let us know if this works
I figured it out. I set the properties in the row edge to everything except the calculation itself :).
So on the Row edge (Camping Equipment) I already associated Actual and Budget in the Data properties. Once I set % at intersection (the calculation) the calculation appeared.
I also tried changing the sort order to 2 and changing around the aggregate function and rollup properties to check if that would change it and suprisingly not even the solve order made a difference for this situation.
Thanks for the thoughts!