none
Aggregate MDX calculated measure RRS feed

  • Question

  • Dear all,

    I'm new in SSAS and MDX, so I need some Help. 

    I have two calculated measures which depends on the value of an other calculated measure.

    CREATE MEMBER CURRENTCUBE.[Measures].[Unterdispo]
     AS iif([Measures].[Kundenbedarf] < 0
        , [Measures].[Kundenbedarf]
        , NULL), 
    FORMAT_STRING = "#,##0.00;-#,##0.00", 
    VISIBLE = 1 ,  DISPLAY_FOLDER = 'Planungsliste';  
    CREATE MEMBER CURRENTCUBE.[Measures].[Ueberdispo]
     AS iif([Measures].[Kundenbedarf] > 0
        , [Measures].[Kundenbedarf]
        , NULL), 
    FORMAT_STRING = "#,##0.00;-#,##0.00", 
    VISIBLE = 1 ,  DISPLAY_FOLDER = 'Planungsliste';  

    If I drill down to the lowest level, the value is right. But I need to aggregate the value from the lowest level:

    Item 1: Kundenbedarf = 100, Unterdispo = 0, Ueberdispo = 100
    Item 2: Kundenbedarf = -20, Unterdispo = -20, Ueberdispo = 0
    Item 3: Kundenbedarf = 30, Unterdispo = 0, Ueberdispo = 30

    Now the result in the level above is:

    Item 1: 100  Unterdispo:   0   Ueberdispo: 100
    Item 2: -20  Unterdispo: -20   Ueberdispo:   0
    Item 3:  30  Unterdispo:   0   Ueberdispo:  30 
    ----------------------------------------------
    Sum   : 110  Unterdispo:   0   Ueberdispo: 110

    But the result should be:

    Item 1: 100  Unterdispo:   0   Ueberdispo: 100
    Item 2: -20  Unterdispo: -20   Ueberdispo:   0
    Item 3:  30  Unterdispo:   0   Ueberdispo:  30 
    ----------------------------------------------
    Sum   : 110  Unterdispo: -20   Ueberdispo: 130

    How do I need to define the calculated measure for this result?

    Thanks a lot!

    Markus


    Tuesday, January 20, 2015 2:12 PM

Answers

All replies

  • This blog post should help you out:

    https://cwebbbi.wordpress.com/2013/05/29/aggregating-the-result-of-an-mdx-calculation-using-scoped-assignments/

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Tuesday, January 20, 2015 2:27 PM
    Moderator
  • Hi,

    couldn't you move the calculation to the DSV / Datasource. Its a leaf level calculation?

    The it would be available as normal measure and would aggregate correctly

    KR Jürgen


    • Edited by yger Tuesday, January 20, 2015 2:31 PM
    Tuesday, January 20, 2015 2:30 PM
  • Hi Chris,

    thank you for your reply. I've found this site by searching the issue in google, but I did not understand it. 

    I understand that I have to use DESCENDANTS, ok, but I do not know how. As I said - I'm realy new in MDX.

    The lowest level which I have to use is called "SKU". The Hirarchy is:

    Item Cat 1
         Item Cat 2
              Item Cat 3
                   Item Cat 4
                        Productmaster
                             SKU

    So I try to use the following code:

    CREATE MEMBER CURRENTCUBE.[Measures].[Unterdispo]
     AS 
        SUM(
            DESCENDANTS([Produkte].CURRENTMEMBER, [Produkte].[SKU]), 
            iif([Measures].[] < 0
            , [Measures].[Kundenbedarf]
            , NULL)),
    FORMAT_STRING = "#,##0.00;-#,##0.00", 
    VISIBLE = 1 ,  DISPLAY_FOLDER = 'Planungsliste';  

    Now I've to wait to get the processing of the cube finished.

    Regards

    Markus


    Tuesday, January 20, 2015 2:53 PM
  • Hi Jürgen,

    sorry about my English, I'm from Austria.

    It is not possible to move the calculation to the DSV. The base for the calculation is also a calculated measure, which depends on other calculated measures ...

    I was not the developer of our BI cubes. My collegue which did this left the company. So I have to support his code.

    Regards

    Markus

    Tuesday, January 20, 2015 3:00 PM
  • Well, no - what I say in that post is that using the Descendants() function can give you the correct results it can also lead to slow query performance. Using a SCOPE statement can perform much better, although that is more advanced MDX. You can learn more about SCOPE statements from this video:

    http://sqlbits.com/Sessions/Event8/Fun_with_Scoped_Assignments_in_MDX

    HTH,

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Tuesday, January 20, 2015 3:01 PM
    Moderator
  • No Problem. I'm also from Vienna :)
    Tuesday, January 20, 2015 3:11 PM