none
MDX total aggregation (sum) doesn't update when filtering RRS feed

  • Question

  • Hello,


    I cannot resolve a problem that is caused by incorrect aggregation on Year level. 

    In below table there are measures. The first has been created on a fact table named  No ATMs and second as calculation that using values from  measure No ATMs.

    Issue is related to second one.

    Please have a look on below table. I deselected three last months in 2018 year. Aggregation in column C is correct, in D is incorrect. Total amount is computed using all months in 2018 unfortunately. In  column E there is expected calculation.

    

    my code:

    case

    when [Time].[YMD].CurrentMember.level.Ordinal = 3
    then 
    sum( [measures].[No ATMs] )

    when [Time].[YMD].CurrentMember.level.Ordinal = 2
    then 
    sum( tail( EXISTING [Time].[YMD].CurrentMember.Children, 1), [measures].[No ATMs] ) 

    when [Time].[YMD].CurrentMember.level.Ordinal = 1
    then 

    --( EXISTING descendants ( [Time].[YMD].[Nazwa Miesiac],0),  [measures].[No ATMs] )
    --sum(EXISTING ( [Time].[YMD].[Nazwa Miesiac],  [measures].[No ATMs] ))

    sum( EXISTING descendants ( [Time].[YMD].[Nazwa Miesiac],0),  [measures].[No ATMs] )
    else NULL
    end

    Tomek



    • Edited by ziembol1977 Friday, October 18, 2019 2:52 PM
    Friday, October 18, 2019 9:35 AM

Answers

  • Hi Tomek,

    Thanks for posting here.

    Per your description, you need to create dynamic SET to overcome that issue. Please refer:

     CREATE DYNAMIC SET [ExistingYMDTwo]  AS
     tail( EXISTING [Time].[YMD].CurrentMember.Children, 1)
    
     CREATE DYNAMIC SET [ExistingYMDOne] AS 
     EXISTING descendants ( [Time].[YMD].[Nazwa Miesiac],0)
    
     --So the MDX expression for the measure should be:
     case when [Time].[YMD].CurrentMember.level.Ordinal = 3
          then sum( [measures].[No ATMs] )
          when [Time].[YMD].CurrentMember.level.Ordinal = 2
          then sum([ExistingYMDTwo], [measures].[No ATMs] ) 
          when [Time].[YMD].CurrentMember.level.Ordinal = 1
          then 
           --( EXISTING descendants ( [Time].[YMD].[Nazwa Miesiac],0),  [measures].[No ATMs] )
           --sum(EXISTING ( [Time].[YMD].[Nazwa Miesiac],  [measures].[No ATMs] ))
          sum( [ExistingYMDOne],  [measures].[No ATMs] )
          else NULL
     end 

    Reference

    SSAS Dynamic Named Sets in Calculated Members

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by ziembol1977 Monday, October 21, 2019 9:26 AM
    Monday, October 21, 2019 3:00 AM
  • :)

    I need one small modification for that code because at the moment total value for year  is correct but on month level I obtain the same values as for December -what is incorrect.

    Tomek

    • Marked as answer by ziembol1977 Monday, October 21, 2019 9:26 AM
    Monday, October 21, 2019 8:29 AM
  • fortunately I solved mentioned issue as it turned out that refer to a measure:

    case
          when [Time].[YMD].CurrentMember.level.Ordinal = 3
          then sum( [measures].[No ATMs] )

          when [Time].[YMD].CurrentMember.level.Ordinal = 2
          then sum([Time].[YMD].CurrentMember, [measures].[No ATMs] )
     
          when [Time].[YMD].CurrentMember.level.Ordinal = 1
          then 
          sum( [ExistingYMDOne],  [measures].[No ATMs] )
          else NULL
     end

    .

    thank you for help

    regards,

    Tomek

    • Marked as answer by ziembol1977 Monday, October 21, 2019 9:26 AM
    Monday, October 21, 2019 9:25 AM

All replies

  • Hi Tomek,

    Thanks for posting here.

    Per your description, you need to create dynamic SET to overcome that issue. Please refer:

     CREATE DYNAMIC SET [ExistingYMDTwo]  AS
     tail( EXISTING [Time].[YMD].CurrentMember.Children, 1)
    
     CREATE DYNAMIC SET [ExistingYMDOne] AS 
     EXISTING descendants ( [Time].[YMD].[Nazwa Miesiac],0)
    
     --So the MDX expression for the measure should be:
     case when [Time].[YMD].CurrentMember.level.Ordinal = 3
          then sum( [measures].[No ATMs] )
          when [Time].[YMD].CurrentMember.level.Ordinal = 2
          then sum([ExistingYMDTwo], [measures].[No ATMs] ) 
          when [Time].[YMD].CurrentMember.level.Ordinal = 1
          then 
           --( EXISTING descendants ( [Time].[YMD].[Nazwa Miesiac],0),  [measures].[No ATMs] )
           --sum(EXISTING ( [Time].[YMD].[Nazwa Miesiac],  [measures].[No ATMs] ))
          sum( [ExistingYMDOne],  [measures].[No ATMs] )
          else NULL
     end 

    Reference

    SSAS Dynamic Named Sets in Calculated Members

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by ziembol1977 Monday, October 21, 2019 9:26 AM
    Monday, October 21, 2019 3:00 AM
  • :)

    I need one small modification for that code because at the moment total value for year  is correct but on month level I obtain the same values as for December -what is incorrect.

    Tomek

    • Marked as answer by ziembol1977 Monday, October 21, 2019 9:26 AM
    Monday, October 21, 2019 8:29 AM
  • Hi Tomek,

    Thanks for your reply.

    Not clear what you have said. Has the original question been solved? Why not use some sample data to express your thought?

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 21, 2019 8:57 AM
  • fortunately I solved mentioned issue as it turned out that refer to a measure:

    case
          when [Time].[YMD].CurrentMember.level.Ordinal = 3
          then sum( [measures].[No ATMs] )

          when [Time].[YMD].CurrentMember.level.Ordinal = 2
          then sum([Time].[YMD].CurrentMember, [measures].[No ATMs] )
     
          when [Time].[YMD].CurrentMember.level.Ordinal = 1
          then 
          sum( [ExistingYMDOne],  [measures].[No ATMs] )
          else NULL
     end

    .

    thank you for help

    regards,

    Tomek

    • Marked as answer by ziembol1977 Monday, October 21, 2019 9:26 AM
    Monday, October 21, 2019 9:25 AM