Hello. I am trying to do a crosstab report off of a cube. I have a Business Unit dimension, Project Type dimension and Revenue measure. I want to be able to calculate the percent of revenue against total revenue. I have figured out how to do this by [Revenue]/total([Revenue] for report]). What I can't figure out is that I need to be able to filter on Project Type, but still display the revenue as a percentage of the total revenue in the cube, not just the total revenue for the project type that was selected.
So if there was no filter on project type, I might have this situation
Business Unit Project Type Revenue Revenue %
BU1 PT 1 100 16.7%
BU1 PT 2 200 33.3%
BU2 PT 1 100 16.7%
BU2 PT 2 200 33.3%
And if I filtered on project type, I'd want to see
Business Unit Project Type Revenue Revenue %
BU1 PT 1 100 16.7%
BU2 PT 1 100 16.7%
but what I am getting now is
Business Unit Project Type Revenue Revenue %
BU1 PT 1 100 50%
BU2 PT 1 100 50%
Thanks in advance for any help
Read up on (in Help and/or User Guide), and use the Tuple() function. It is how you define an explicit intersection for use in such calculations, regardless of where you are in the Crosstab. Wrap Value() around it to get the value for use in calculations.