none
How to sum quantity with Price = 0$ in MDX?

    Question

  • Invnetory Name Quantity Price Amount
    DELL 2 50$
    SONY VAIO 12 100$
    LENOVO 4 12$
    SAMSUNG 5 11$
    SAMSUNG1 12 0$
    DELL1 16 0$
    How to sum quantity with Price count = 0 in MDX?
    Invnetory Name Quantity Price Amount
    SAMSUNG1 12 0$
    DELL1 16 0$
    Grand Total 28 0$
    When it show in excel sum(Quantity) always equal 2+12+4+5+12+16. But I want 12+16 with condittion Price = 0$
    hELP mE,please!
    tHANKS!
    Wednesday, October 09, 2013 7:20 AM

Answers

  • Hello ,

    So if you want your measure to function as summing the quantity for price = 0, you need to define a new measure in your cube (calculation tab) that will refer the set we've created :

    Create member CURRENTCUBE.[Measures].[MyNewQuantity]
    As (SUM(
      FILTER([Invnetory].[Invnetory Name].[Invnetory Name].members,
        [Price Amount] = 0),
      [Quantity]));

    Hope it helps :)


    Regards, David .

    Thursday, October 10, 2013 11:55 AM

All replies

  • Hi ,

    Try to use the filter function ...

    WITH
    MEMBER [QuantityZeros] AS
    SUM(
        FILTER([Invnetory].[Invnetory Name].[Invnetory Name].members,
            [Price Amount] = 0),
        [Quantity])

    Hope it helps :)

    Regards, David .

    Wednesday, October 09, 2013 3:56 PM
  • Thanks David!,

    But i wants it shows in excel.

    Can you help me, please!

    Thanks Again!

    Thursday, October 10, 2013 7:24 AM
  • Hello ,

    So if you want your measure to function as summing the quantity for price = 0, you need to define a new measure in your cube (calculation tab) that will refer the set we've created :

    Create member CURRENTCUBE.[Measures].[MyNewQuantity]
    As (SUM(
      FILTER([Invnetory].[Invnetory Name].[Invnetory Name].members,
        [Price Amount] = 0),
      [Quantity]));

    Hope it helps :)


    Regards, David .

    Thursday, October 10, 2013 11:55 AM