locked
Facing problem with GrandTotal valule for calucated members. RRS feed

  • Question

  • I have created calclulation by using following MDX Query.
     
    CREATE MEMBER CURRENTCUBE.[Measures].[Total Call Average time]
     AS Round((([Measures].[Total Call tm])/([Measures].[Incident Count])),0),
     Visible = 1;
     
    Wheile browsing the values in grand total also its calculating as GrandTotal[Measures].[Total Call tm])/GrandTotal([Measures].[Incident Count])
     
    RowLabels  TotalCallTm IncidentCount    TotalCallAveragetime
     
    911Lines     12368796   76                    162747
    InPerson      4323430    7                      617633
    GrandTotal  16692226   83                  201111

    Is there any chance to get GrandTotal currect value?


    madhu ml

    Tuesday, August 7, 2012 11:17 AM

Answers

  • So the SCOPE aisgnments will not work correctly for the Calculated Member. As in the thread mentioned a physical dimension needs to be created so everything is correctly aggregated up. So you have to create a [Total Call Average Time] as physical dimension and then the Scoped Cell calculatino should look like:

    Scope ([Measures].[Total Call Average time], Leaves([CallTypeDImension]), Leaves([DateDimension]));
    	This = [Measures].[Total Call tm]/[Measures].[Incident Count];
    End Scope;

    In case for your dimension representing Call Types has only the [All] Member and child  are tye types, then you can create a scoped calculation only for this scenario and it will work even with the calculated member. You simply overide the top level aggregated value.

    SCOPE([Measures].[Total Call Average time], Root([CallTypeDimension]));
    	THIS = AGGREGATE([CallTypeDimension].[CallTypesHierarchy].MEMBERS, Measures].[Total Call tm]) /
    		AGGREGATE([CallTypeDimension].[CallTypesHierarchy].MEMBERS, Measures].[Incident Count])
    END SCOPE;

    • Proposed as answer by Elvis Long Monday, August 13, 2012 9:56 AM
    • Marked as answer by Elvis Long Thursday, August 16, 2012 1:05 AM
    Wednesday, August 8, 2012 8:44 AM

All replies

  • What is wrong with that? It is giving you the average call time across all the calls, which to me would be correct.

    What is it that you want the grand total to return then?

    Tuesday, August 7, 2012 11:45 AM
  • This is duplicate question of this one: http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/c58a0f84-10b7-4010-b2f4-7bf52a91965a which is already sloved and answered. Probably this can be deleted by moderators.


    Tuesday, August 7, 2012 12:53 PM
  • Hi Pavel, Not yet solved. Please suggest me.

    madhu ml

    Tuesday, August 7, 2012 3:04 PM
  • Other option is to introduce a calculated field in the Data Source View in your fact table and than create a regular measure. This will give you the best query performance.
    Wednesday, August 8, 2012 6:01 AM
  • Hi Madhu

    What is it that you actually want the grand total to display if not what it is doing? A sum of averages is pretty meaningless, and if you want to take an average of the averages, that to becomes heavily weighted?

    • What you currently have seems to me, to be the correct value you want as the overall average of all the calls.
    • If you want an average of the averages (ave(162747,617633) = 390190, you are getting a weighted average? Don't see the use of it for trying to determine anything?
    • If you want a sum of the averages (162747 + 617633 = 780380), I also don't see what that is giving you

    Without any ideas as to what you want the grand total to display, I don't see how I can help you.

    Mike

    Wednesday, August 8, 2012 7:35 AM
  • Hi Pavel,

    I am following the same way. But with this query which was sent by you is giving error. Can you please send exact matched Scope funtion for my following MDX query.

    CREATE MEMBER CURRENTCUBE.[Measures].[Total Call Average time]
    AS Round((([Measures].[Total Call tm])/([Measures].[Incident Count])),0),
    Visible = 1;


    madhu ml

    Wednesday, August 8, 2012 7:50 AM
  • Hi Michael,

    I am excepting the Total(Average values) in garand total field.


    madhu ml

    Wednesday, August 8, 2012 7:52 AM
  • Hi Madhu

    I assume that by Total() you mean sum?

    You will need to Scope the all values then, and probably need to include the Existing() to get the correct sum. I don't see what value that is going to give you though, as it is a pretty meaningless value:

    So could try:

    SCOPE([Measures].[Total Call Average time]);
        SCOPE(Dimension.Dimension.All);
            THIS = SUM(Existing(Dimension.Dimension.Dimension.Members), [Measures].[Total Call Average time]);
        END SCOPE;
    END SCOPE;

    Wednesday, August 8, 2012 8:44 AM
  • So the SCOPE aisgnments will not work correctly for the Calculated Member. As in the thread mentioned a physical dimension needs to be created so everything is correctly aggregated up. So you have to create a [Total Call Average Time] as physical dimension and then the Scoped Cell calculatino should look like:

    Scope ([Measures].[Total Call Average time], Leaves([CallTypeDImension]), Leaves([DateDimension]));
    	This = [Measures].[Total Call tm]/[Measures].[Incident Count];
    End Scope;

    In case for your dimension representing Call Types has only the [All] Member and child  are tye types, then you can create a scoped calculation only for this scenario and it will work even with the calculated member. You simply overide the top level aggregated value.

    SCOPE([Measures].[Total Call Average time], Root([CallTypeDimension]));
    	THIS = AGGREGATE([CallTypeDimension].[CallTypesHierarchy].MEMBERS, Measures].[Total Call tm]) /
    		AGGREGATE([CallTypeDimension].[CallTypesHierarchy].MEMBERS, Measures].[Incident Count])
    END SCOPE;

    • Proposed as answer by Elvis Long Monday, August 13, 2012 9:56 AM
    • Marked as answer by Elvis Long Thursday, August 16, 2012 1:05 AM
    Wednesday, August 8, 2012 8:44 AM