Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Calculated Measure - "% of Grand Total"

Answered Calculated Measure - "% of Grand Total"

  • Wednesday, March 20, 2013 2:22 PM
     
     
    Sign In to Vote
    0

    Hello,

    I am trying to replicate the way Excel's pivot "% of Grand Total" works but use it in a calculated measure.

    For example, I have 2 dimensions (Product, Team) and 1 fact table. Let's say I have a [Product].[Product Name] along the rows and then a hierarchy of Team (sub-team, sub-sub-team) along the columns. There is an amount measure associated to the dimensions. This is all filtered by day. When I put this in excel and then choose "% of Grand Total" it works perfectly and is exactly what I need. The amount is divided by the total amount per day and each drill down amount is also divided by the total amount per day, which is exactly what I need.

    Now when I instead use a subset to find the Top 5 products instead of using the entire [Product].[Product Name], "% of Grand Total" no longer works. What I need is to create a calculated measure that works similarly to "% of Grand Total" or find a way to divide the [Measure].[Amount] by the total amount per day.

    For example, let's say for 1/02/2013 the total amount was 100. I need to find the amount/100. Numerator will be [measure].[amount] while the denominator will be total amount of that day.

    Date: 1/02/2013

                        Team1          Team2          Team3          Grand Total

    Product1      20/100          15/100         5/100           40/100

    Product2      5/100            10/100         20/100          35/100

    Product3      10/100          15/100          0/100           25/100

    GrandTotal   35/100          40/100          25/100         100/100

    Then if you drill down, the sub teams of Team1 should add up to 20/100, etc. So if there are two sub teams for Team1, one may have 5/100 and the other 15/100 for Product1.

    Thanks for any help.

     

All Replies

  • Wednesday, March 20, 2013 3:18 PM
     
     
    Sign In to Vote
    0

    Hi ,

    What i understands from your question is you want grant total selected items. Here is approach

    While using SUBSET function pass second arguments as 0 i.e. subset([Product].[Product Name],0,10)

    so you will get grand total of all product.

    If you want grand total of only selected product then you can filter data using EXCEPT or FILTER function .

    Hope this will help you.

     

    Thanks,

    Swapnil


     
  • Wednesday, March 20, 2013 4:52 PM
     
     
    Sign In to Vote
    0

    Thanks for your help. It got me thinking about how the sets work. So I think I am making progress.

    I am using:

    CREATE MEMBER [Measures].[Amount_percentage] AS

    [Measures].[Amount] / SUM( ([Date].[Date], [Product].[Product Name].[All], [Team].[Team Hierarchy].[All]), [Measures].[Amount] )

    *Later I would prefer if I could do the .[All] dynamically (using Axis() function)*

    and getting the correct values. This is dividing the amount by the total day amount. This is working on standard fields. I am now introducing a Top 5 product named set: CREATE SET [Top 5 Product] AS TOPCOUNT( [Product].[Product Name].CHILDREN,5,[Measures].[Amount].

    Using [Team].[Team Hierarchy] ON columns and [Top 5 Product] ON rows and [Measures].[Amount_percentage]  as my values. I need to filter this by certain product classifications. What is the best way to filter [Measure].[Amount] so I do not pick up amounts in certain classifications? Also, it cannot affect my denominator, as I still need to pick up all amounts for the entire day.


    EDIT:

    I figured out the filter and came up with this named set:

    TOPCOUNT( FILTER([Product].[Product Name].CHILDREN,
                [Product].[Classification].&[Class1] or [Product].[Classification].&[Class2]
                or [Product].[Classification].&[Class3] or [Product].[Classification].&[Class4]),
    25,

    CASE WHEN
        ISEMPTY( [Measures].[Amount] )
    THEN
        0
    ELSE [Measures].[Amount]
    END

    HOWEVER, it still takes the amount values from the classifications I do not want. How do I filter the [Measure].[Amount] values to only the 4 classes I want?

    One last question to ask..for my calculated measure:

    CREATE MEMBER [Measures].[Amount_percentage] AS

    [Measures].[Amount] / SUM( ([Date].[Date], [Product].[Product Name].[All], [Team].[Team Hierarchy].[All]), [Measures].[Amount] )

    How do I use the Axis() function so that I do not have to use specific members of the dimensions? Is there a way to do something like Axis(0).Item(0).Item(0).Dimension.All? Keep in mind, what I am trying to get out of the denominator is the total amount under all dimensions for the day.



    • Edited by AndrewOwns Wednesday, March 20, 2013 8:15 PM
    •  
     
  • Friday, March 22, 2013 5:14 PM
     
     Answered
    Sign In to Vote
    0

    Just wanted to reply that I found an answer just in case someone else could use it. I had to create a named calculation that duplicated my Amount measure, Amount_filtered. I then used that with the SCOPE function in order to filter the measure and divided it with the original total amount, SUM( ([Date].[Date], [Product].[Product Name].[All], [Team].[Team Hierarchy].[All]), [Measures].[Amount] ).

    But I do have one question, I didn't want to create another thread. How would I re write the sum above, so it takes into consideration any and all dimension possibilities? So I just want the total Amount sum of the day, not broken down into any dimensions.