What I am trying to do it get the total Premium for Calendar MTD based on a measure I created (comm_prem - new_bus). I also need Premium for Calendar PY MTD - but I dont' want to use the same measure, I need to just use comm_prem.
Then when I have these 2 amounts, I would want a Percent Growtch.
I figured this might have to be done via MDX.
This is what I get:
Existing Bus Comm_premium
Calendar MTD Calendar PY MTD Calendar MTD Calendar PY MTD
Sales Rep 1 Oct 2009 I WANT THIS Don't want this Don't want this I WANT THIS
Since the Hirearchy is selecing Calendar MTD and Caleandar PY MTD, which I need both, but only want to display Calandar MTD for Existing_Bus and Calendar PY MTD for Comm_Prem
This is how the MDX looks via ProClarity. Note the Member Existing Business is the one that needs to be calculated differntly.
SELECT
NON EMPTY { { { [<##<MEMBER!svoth!Existing Business>##>], [Measures].[Commissionable Premium] } * { [Written Date].[Date Calculations].[Calendar MTD], [Written Date].[Date Calculations].[Calendar PY MTD] } } } ON COLUMNS ,
NON EMPTY { { { [Agency].[Sales Rep].[All Agencies].CHILDREN } * { [Written Date].[Calendar].[Month].&[200910] } } } ON ROWS
FROM [Sales]
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL
Any help?