none
MDX - Sum by a specific dimension

    Question

  • HI,

    I Need your help please!

    In my cube browser, I am showing the below results:

    From Date          To Date                Amount               Sum Amount per From Date – Needed one

    2018-05-01          2018-05-02         100                        1372

    2018-05-01          2018-05-04         63                           1372

    2018-05-01          2018-05-05         45                           1372

    2018-05-01          2018-05-10         412                        1372

    2018-05-01          2018-05-17         752                        1372

    2018-05-06          2018-05-11         400                         2770

    2018-05-06          2018-05-15         752                        2770

    2018-05-06          2018-05-16         4                             2770

    2018-05-06          2018-05-20         754                        2770

    2018-05-06          2018-05-21         12                           2770

    2018-05-06          2018-05-26         782                        2770

    2018-05-06          2018-05-27         66                           2770

     

    How to create a calculated member to show the column ‘’Sum Amount per From Date’’?

    Thanks a lot!
    Wednesday, June 13, 2018 3:24 PM

Answers

  • WITH MEMBER [Measures].[Sum Amount per From Date]

    AS

    ([Measures].[Amount],[DimDate].[To Date].[All])


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Wednesday, June 13, 2018 3:33 PM
  • Hi Karim,

    Thanks for your question.

    I assume From Date and To Date are role playing dimension in your cube, then you might want to try blow MDX expression:

    [Measures].[Sum Amount per From Date] AS
    (Root(([To Date].[To Date].[All],
           [From Date].[From Date].currentMember)),
           [Measures].[Amount])
    
    OR
    
    [Measures].[Sum Amount per From Date] AS
    ([To Date].[To Date].[All],
            [Measures].[Amount])
    
    See below MDX query tested in cube AdventureWorks:
    With 
    Member [Measures].[AmountIngoreCAT] As
    ( [Product].[Category].[All],
    [Measures].[Reseller Sales Amount])
    
     Member [Measures].[AmountIngoreCAT1] As
    (Root(([Product].[Category].[All],[Date].[Date].currentMember)),
           [Measures].[Reseller Sales Amount])
    
    select  {[Measures].[Reseller Sales Amount],
             [Measures].[AmountIngoreCAT],
    		 [Measures].[AmountIngoreCAT1] }  on 0,
    non empty {[Date].[Date].[Date]*
          [Product].[Category].[Category]}on 1
    From [Adventure Works]
    where
    [Date].[Calendar Year].&[2012]



    Best Regards
    Willson Yuan
    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

    Thursday, June 14, 2018 1:58 AM
    Moderator

All replies

  • WITH MEMBER [Measures].[Sum Amount per From Date]

    AS

    ([Measures].[Amount],[DimDate].[To Date].[All])


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Wednesday, June 13, 2018 3:33 PM
  • Hi Karim,

    Thanks for your question.

    I assume From Date and To Date are role playing dimension in your cube, then you might want to try blow MDX expression:

    [Measures].[Sum Amount per From Date] AS
    (Root(([To Date].[To Date].[All],
           [From Date].[From Date].currentMember)),
           [Measures].[Amount])
    
    OR
    
    [Measures].[Sum Amount per From Date] AS
    ([To Date].[To Date].[All],
            [Measures].[Amount])
    
    See below MDX query tested in cube AdventureWorks:
    With 
    Member [Measures].[AmountIngoreCAT] As
    ( [Product].[Category].[All],
    [Measures].[Reseller Sales Amount])
    
     Member [Measures].[AmountIngoreCAT1] As
    (Root(([Product].[Category].[All],[Date].[Date].currentMember)),
           [Measures].[Reseller Sales Amount])
    
    select  {[Measures].[Reseller Sales Amount],
             [Measures].[AmountIngoreCAT],
    		 [Measures].[AmountIngoreCAT1] }  on 0,
    non empty {[Date].[Date].[Date]*
          [Product].[Category].[Category]}on 1
    From [Adventure Works]
    where
    [Date].[Calendar Year].&[2012]



    Best Regards
    Willson Yuan
    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

    Thursday, June 14, 2018 1:58 AM
    Moderator
  • Thanks a lot!

    Karim El Guinady

    Friday, June 15, 2018 4:31 PM
  • Thanks a lot for your detailed/clear answer!

    I am still stuck at my last step, I am trying to create a calculated member showing totals per date from but I should pass through Date to first like below:

    DateFrom

    DateTo

    DateDiff

    Amount1

    DateDiff*Amount1

    TotalPerDateFrom

    01/05/2018

    03/05/2018

    2

    300

    600

    1050

    01/05/2018

    04/05/2018

    3

    150

    450

    06/05/2018

    09/05/2018

    3

    33

    99

    224

    06/05/2018

    11/05/2018

    5

    25

    125

    but when i remove the Date to the concept changed as per below :

    DateFrom

    AVGDateDiff

    Amount1

    DateDiff*Amount1

    01/05/2018

    2,5

    450

    1125 (wrong)

    06/05/2018

    4

    58

    232 (wrong)

    How to calculate [date diff] * [amount1] grouped by [date to] before the sum by [Date from]

    Thanks a lot!


    Karim El Guinady

    Friday, June 15, 2018 5:08 PM