Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Struggling Creating Calculated Measure Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, May 02, 2013 1:03 PM
 Forum Newbie Group: General Forum Members Last Login: Friday, June 14, 2013 7:55 AM Points: 6, Visits: 27
 I’m struggling implementing a calculated measure in one of my cubes. Basically I need to take two measures, divide them for a given period, and them sum the results together. For example, if the user selects 2012 as the year, I need to do the following:(Q1, Q2, etc. are children under 2012 in my date.calendar dimension hierarchy)(Q1 Purchases / Q1 Base) +(Q2 Purchases / Q2 Base) +(Q3 Purchases / Q3 Base) +(Q4 Purchases / Q4 Base) +But this needs to work dynamically based on whether the user selects multiple years, a single year, a single quarter, independent quarters for any given years, etc. Anyone have any ideas?Thanks in advance,Jason
Post #1448955
 Posted Friday, May 03, 2013 8:55 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, May 20, 2013 2:14 AM Points: 4, Visits: 31
 Replace [Internet Order Count] with baseand [Measures].[Customer Count] with purchasesfrom the below example.I have created a [hidden measure] which calculates the purchase/base for every date member.The [RequiredMeasure] with sum the children which will result in the calculation like you requested.CREATE MEMBER CURRENTCUBE.[Measures].[HiddenMeasure] AS null, VISIBLE = 1 ; SCOPE([Date].[Calendar].MEMBERS ,[Measures].[HiddenMeasure]); THIS=iif([Measures].[Internet Order Count]=0,NULL,[Measures].[Customer Count]/[Measures].[Internet Order Count]) ;END SCOPE; CREATE MEMBER CURRENTCUBE.[Measures].[RequiredMeasure] AS null, VISIBLE = 0 ; SCOPE([Date].[Calendar].MEMBERS ,[Measures].[RequiredMeasure]); THIS=sum([Date].[Calendar].currentmember.children,[Measures].[HiddenMeasure] );END SCOPE; Hope it helps
Post #1449252
 Posted Friday, May 03, 2013 8:59 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, June 14, 2013 7:55 AM Points: 6, Visits: 27
 moviesvj, thanks for the reply. Works great!
Post #1449254

 Permissions