none
MDX - How to SUM a calculated measure - only if value is greater than a given value say 1000 RRS feed

  • Question

  • Hi

    I have a measure group with an [Amount] - currency measure.

    I want to create a calculated measure to SUM the [Amount] - but ONLY those where the individual [Amount]'s are greater than a specific value such as 1000.

    How can I build a measure to SUM with an IF statement?


    I.W Coetzer

    Thursday, August 15, 2019 3:33 PM

Answers

  • So modify the syntax of MDX query like this.

    CREATE MEMBER CURRENTCUBE.[Measures].[CountNew]
     AS 
    SUM(
    FILTER
        (
        EXCEPT([Period].[Period].MEMBERS,[Period].[Period].[ALL]), 
        [Measures].[Contract Count] >= 1
        ),[Measures].[Contract Count]
        )
    
    , 
    VISIBLE = 1  ; 

    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 I.W Coetzer Friday, August 16, 2019 7:47 AM
    Friday, August 16, 2019 7:14 AM

All replies

  • Hi

    I have this exact requirement ...

    the first measure 'Contract Count' is the actual measures in my measure group.

    In this case I have only two measure records

    1. 2019/01 for group A

    2. 2019/03 for group B

    I am trying to build an MDX calculated measure called [Count] (seen in 2nd column) that will carry the measures in the measure group 'forward'

    2019/01 - should have a Count 1 but only for A

    2019/02 - should have a Count 1 but only for A

    2019/03 - should have a Count 1 but only for B

    2019/04 - should have a Count 1 but only for B

    2019/05 - should have a Count 1 but only for B

    2019/06 - should have a Count 1 but only for B

    2019/07 - should have a Count 1 but only for B


    I.W Coetzer

    Thursday, August 15, 2019 4:12 PM
  • Hi

    I have a measure group with an [Amount] - currency measure.

    I want to create a calculated measure to SUM the [Amount] - but ONLY those where the individual [Amount]'s are greater than a specific value such as 1000.

    How can I build a measure to SUM with an IF statement?


    I.W Coetzer

    Hi Coetzer,

    You could build a measure like this.

    CREATE MEMBER [CubeName].[Measures].[ConditionAmount]
    AS
    SUM(FILTER([DimensionName].[AttributeName].MEMBERS,[Measures].[Amount]>1000),[Measures].[Amount])

    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.

    Friday, August 16, 2019 6:04 AM
  • I tried your suggestion Will but the results are strange:

    It returns a number of 4?

    CREATE MEMBER CURRENTCUBE.[Measures].[CountNew]
     AS 
    SUM(
    FILTER
        (
        [Period].[Period].MEMBERS, 
        [Measures].[Contract Count] >= 1
        ),[Measures].[Contract Count]
        )
    
    , 
    VISIBLE = 1  ; 



    I.W Coetzer

    Friday, August 16, 2019 6:14 AM
  • Hi I.W Coetzer,

    Please show us the result of the following MDX query.

    SELECT 
    [Measures].[Contract Count] ON 0,
    FILTER
        (
        [Period].[Period].MEMBERS, 
        [Measures].[Contract Count] >= 1
        ) ON 1
    FROM CUBENAME

    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.

    Friday, August 16, 2019 6:59 AM
  • Hi Will

    Please see below - P.S I have to filter on one contract number specifically since I was doing that in my original result.


    I.W Coetzer

    Friday, August 16, 2019 7:07 AM
  • So modify the syntax of MDX query like this.

    CREATE MEMBER CURRENTCUBE.[Measures].[CountNew]
     AS 
    SUM(
    FILTER
        (
        EXCEPT([Period].[Period].MEMBERS,[Period].[Period].[ALL]), 
        [Measures].[Contract Count] >= 1
        ),[Measures].[Contract Count]
        )
    
    , 
    VISIBLE = 1  ; 

    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 I.W Coetzer Friday, August 16, 2019 7:47 AM
    Friday, August 16, 2019 7:14 AM