none
Create Calculated Member Based on Total RRS feed

  • Question

  • I'm trying to create a calculated member that is based off a total column. I've attached a screen shot. Essentially, I'm trying to create the calculated member off of the SSAS generated total for the members in the cube. There are three categories: ADMIN, PTO, and PRODUCTION. For each of these categories, there is a measure called EMP HOURS, which is a sum of all the hours that employee worked in that category.

    I would like to calculate the percentage by taking the amount in the EMP HOURS cell and divide it by the amount in the TOTAL cell (total hours).

    I'd like to calculate a percentage of the total hours but not sure how to go about it.

    If someone could provide some insight, that would be fantastic!

    Thanks!!


    A. M. Robinson
    Saturday, September 17, 2011 2:27 PM

All replies

  • Hi A.M.

    Try this one out, and notice that results depend on the Query Scope and context..

    CREATE MEMBER CURRENTCUBE.[Measures].[HoursPercentage]

    AS [Measures].[EMP Hours]/ ( [DIM TIME CATEGORY].[UTILIZATION CATEGORY].[All] , [Measures].[EMP Hours] )

    ,FORMAT_STRING = "Percent" ;

     

    --

    [DIM TIME CATEGORY].[UTILIZATION CATEGORY].[All] = The all member of the Utilization category. You should check in your cube if this is the name of the all member..

    • Marked as answer by ansonee Saturday, September 17, 2011 6:36 PM
    • Unmarked as answer by ansonee Tuesday, September 20, 2011 2:04 PM
    Saturday, September 17, 2011 4:01 PM
  • Kees:

    That worked perfectly! Actually, there is one more level after UTILIZATION CATEGORY called CATEGORY.For example, under the PRODUCTION category, there are three sub-categories: CLIENT FACING, NEW BUSINESS, and INTERNAL. The calculation works perfectly. There is one thing, however, I've been trying to fix but for some reason can't get it to display properly.

    If there is a subcategory that has no data, I'd still like to display that subcategory and just display a 0 and 0%. Actually this goes for all subcategories in eaach category. I'm trying to find a "Display if Empty" setting somewhere, but think I'm looking in tyhe wrong spot.

    I don't suppose you could help with that?

    Thanks again!!!


    A. M. Robinson
    Saturday, September 17, 2011 6:36 PM
  • A.M.

    Great to hear so, to overcome automatic non empty behaviour of the client tools you could alter the calculated member formula slightly

     IIF ( ISEMPTY ([Measures].[EMP Hours]) , 0 , [Measures].[EMP Hours]/ ( [DIM TIME CATEGORY].[UTILIZATION CATEGORY].[All] , [Measures].[EMP Hours] ) )
    

    Above formula checks if the EMP Hours in the curent Query Context are empty (NULL) and assigns 0 resulting in 0%

    Regards Kees

    Sunday, September 18, 2011 9:14 AM
  • Kees:

    Thank you! I need to make one more tweak to this one. The calculation is performing almost perfectly. The one thing I need to adjust is the empty members piece. The empty cells aer displaying, but I'd like to not display those rows where there is no data at the top level (utilization category) but still display zero for those on the next level where the value is zero. For example:

    In the screen shot below, Elen Bahr and Wes Baker have no hours associated with them. I'd like to not display those people. But KAtie Banfield does have hours but not hours in every category. KAtie Banfield is displaying just as I'd like her to.

    Is what I'm looking to do possible?


    A. M. Robinson
    • Edited by ansonee Monday, September 19, 2011 2:56 PM
    Monday, September 19, 2011 2:51 PM