June 28, 2012 at 5:49 am
Dear All,
There was a created member in a SSAS cube which basically calculates the % of CPM revenue over total reveue. Now, I need to do this only for UK campaigns and I am not sure how to put this filter in the calulated member in BIDS. Any help will be much appreciated. Below is the MDX I currently have but I need to add a filter to it so it does the calculation only for UK campaigns [Campaign].[SalesOffice].[Sales Office].&[UK]
Please help. Thanks
CREATE MEMBER CURRENTCUBE.[Measures].PercentageCPM_Revenue
AS IIF
(
[Measures].[Revenue No Over Del] = 0, NULL,
( [Campaign].[Metric].[Campaign Metric].&[CPM], [Measures].[Revenue No Over Del]) /
(
// The Root function returns the (All) value for the target dimension.
Root
(
[Campaign]
),
[Measures].[Revenue No Over Del]
)
),
FORMAT_STRING = "Percent",
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Weekly Forecast By Sales Team' ;
June 29, 2012 at 8:34 am
You already have 1 dimension filter so you should just have to add the next one the same way:
CREATE MEMBER CURRENTCUBE.[Measures].PercentageCPM_Revenue
AS IIF
(
[Measures].[Revenue No Over Del] = 0, NULL,
( [Campaign].[Metric].[Campaign Metric].&[CPM], [Campaign].[SalesOffice].[Sales Office].&[UK]
, [Measures].[Revenue No Over Del]) /
(
// The Root function returns the (All) value for the target dimension.
Root
(
[Campaign]
),
[Measures].[Revenue No Over Del]
)
),
FORMAT_STRING = "Percent",
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Weekly Forecast By Sales Team' ;
If you want the denominator to also be filtered by UK then add [Campaign].[SalesOffice].[Sales Office].&[UK]
again as such:
CREATE MEMBER CURRENTCUBE.[Measures].PercentageCPM_Revenue
AS IIF
(
[Measures].[Revenue No Over Del] = 0, NULL,
( [Campaign].[Metric].[Campaign Metric].&[CPM], [Campaign].[SalesOffice].[Sales Office].&[UK]
, [Measures].[Revenue No Over Del]) /
(
// The Root function returns the (All) value for the target dimension.
Root
(
[Campaign]
),
[Measures].[Revenue No Over Del], [Campaign].[SalesOffice].[Sales Office].&[UK]
)
),
FORMAT_STRING = "Percent",
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Weekly Forecast By Sales Team' ;
June 29, 2012 at 8:39 am
Thank you very much, this was helpful! Now, if I want to get this calculation to work for eveything other than 'UK' campaigns (France, US, Denmark, etc), how do I modify this filter?
Thanks
June 29, 2012 at 8:42 am
In that case you just change the specified member of UK to be the CurrentMember, but then you will have to slice your calculated measure byt he dimension to get the proper results:
CREATE MEMBER CURRENTCUBE.[Measures].PercentageCPM_Revenue
AS IIF
(
[Measures].[Revenue No Over Del] = 0, NULL,
( [Campaign].[Metric].[Campaign Metric].&[CPM], [Campaign].[SalesOffice].[Sales Office].CurrentMember
, [Measures].[Revenue No Over Del]) /
(
// The Root function returns the (All) value for the target dimension.
Root
(
[Campaign]
),
[Measures].[Revenue No Over Del]
)
),
FORMAT_STRING = "Percent",
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Weekly Forecast By Sales Team' ;
June 29, 2012 at 8:45 am
Thanks, I will give that a go!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply