locked
Help creating a calculated member RRS feed

  • Question

  • Hi all, ok so this is driving me friggin nuts so all help will be appreciated.

    I need to create a Utilisation % measure for a cube. This is defined as billable days divided by total days available (which is all days minus absence).  
    I have a fact table at the day grain and I have a days measure. I have managed to get a member that gives me billable days as below: 

    ([Billable Status].[Billable Status].&[1], [Measures].[Days])

    but now I'm stuck, I cant seem to get all days minus absences. So what I've tried to do is create a set as below: 

     EXCEPT([Booking Status].[Status].Members, [Booking Status].[Status].&[13])

    which is all the booking statuses minus the absence member, I have then tried to apply this to the days measure as below:

    ([BookingsExcludingAbsence], [Measures].[Days])

    but this errors saying "Function expects a string or numeric expression. A tuple set expression was used"

    Once I've got that bit working I assume its just going to be a matter of doing something like:

    [Billable Days] / [All Available Days]


    I quite new to mdx and havent 100% got my head round it yet so all help will be appreciated!

    Thanks!!
    Thursday, October 27, 2011 9:41 AM

Answers

  • Hi Tenchijin,

    Its a litte hard to visualise your results without understanding your data but what your trying to do sounds pretty normal.  Perhaps, you could translate it to adventure works and post the query?

    I've attached a query which i think shows what your trying to achieve.  However, in this case, we're just trying to sum all sales except for [bikes].  The results are attached.  The calculated value is consistent regardless of the [category] on the column axis.

    Is that what your trying to achieve?

    HTH

    Paul

    www.paultebraak.wordpress.com

     

     

    with member measures.available_days as 
    sum( except([Product].[Category].[Category],[Product].[Category].&[1])
    	 , [Measures].[Reseller Sales Amount]
    	 )
    	 
    select 
     [Product].[Category].allmembers
      on 0,
    
    {	measures.available_days  
    	,[Measures].[Reseller Sales Amount]
    } on 1
     
     from	[Adventure Works]
    

    • Proposed as answer by Jerry Nee Monday, October 31, 2011 8:28 AM
    • Marked as answer by Challen Fu Monday, November 7, 2011 10:43 AM
    Thursday, October 27, 2011 8:16 PM

All replies

  • Hi,

    I think if you sum the days in the tuple, you should get the [All Available Days]

     [All Available Days] as sum(EXCEPT([Booking Status].[Status].Members, [Booking Status].[Status].&[13]) , [Measures].[Days]).

    HTH

    Paul

    www.paultebraak.wordpress.com

    Thursday, October 27, 2011 10:46 AM
  • Hi,

    I think if you sum the days in the tuple, you should get the [All Available Days]

     [All Available Days] as sum(EXCEPT([Booking Status].[Status].Members, [Booking Status].[Status].&[13]) , [Measures].[Days]).

    HTH

    Paul

    www.paultebraak.wordpress.com

    Hi Paul, thanks for the input and almost there I think! 

    so when applying this to a MDX query I would expect a static result across the row (a row being per resource who's utilisation I am trying to measure) but unfortunately it seems to to be delivering some odd numbers.  If for example I have [Billable Status] (billed & not billed basically) on the columns this value changes between each column where I would expect it to be static for the resource, regardless of what is on the columns like the [Billable Days] measure I created.

     

    Any more advice greatly welcomed :)

    Thursday, October 27, 2011 11:55 AM
  • Hi Tenchijin,

    Its a litte hard to visualise your results without understanding your data but what your trying to do sounds pretty normal.  Perhaps, you could translate it to adventure works and post the query?

    I've attached a query which i think shows what your trying to achieve.  However, in this case, we're just trying to sum all sales except for [bikes].  The results are attached.  The calculated value is consistent regardless of the [category] on the column axis.

    Is that what your trying to achieve?

    HTH

    Paul

    www.paultebraak.wordpress.com

     

     

    with member measures.available_days as 
    sum( except([Product].[Category].[Category],[Product].[Category].&[1])
    	 , [Measures].[Reseller Sales Amount]
    	 )
    	 
    select 
     [Product].[Category].allmembers
      on 0,
    
    {	measures.available_days  
    	,[Measures].[Reseller Sales Amount]
    } on 1
     
     from	[Adventure Works]
    

    • Proposed as answer by Jerry Nee Monday, October 31, 2011 8:28 AM
    • Marked as answer by Challen Fu Monday, November 7, 2011 10:43 AM
    Thursday, October 27, 2011 8:16 PM