Forum Discussion

GuestUser's avatar
GuestUser
Helper V
6 years ago
Solved

Cumulative dax query help

Hi We are using power bi with ssas tabular model using connect live option. Need help in cumulative column Report format is as below Matrix view Year. 2018. 2019 Item. Trxcount. Cumulative T...
Need Help
  • GuestUser's avatar
    GuestUser
    6 years ago

    Hi mwegener 

     

    @Thanks for your inputs!!

    Used below formula(similar)..replaced selected value by hasonevalue and it worked..

     

    Thanks !!

    Measure =
CALCULATE (
    [Sales],
    FILTER (
        ALLSELECTED ( 'Table'[Category] ),
        'Table'[Category] <= SELECTEDVALUE ( 'Table'[Category] )
    )
)

     

Mariusz's avatar
Mariusz
Community Champion
6 years ago

Hi GuestUser 

 

Try this pattern.

Cumulative Quantity :=
CALCULATE (
    SUM ( Transactions[Quantity] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

 Read this article for more info 
https://www.daxpatterns.com/cumulative-total/

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn



GuestUser's avatar
GuestUser
Helper V
6 years ago

Hi Mariusz 

 

have tried this and its giving same value for all items same like transaction count

 

need the cumulative value item wise

 

any suggestion pls

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    Hi GuestUser ,

     

    You need to group by Year with ALLEXCEPT() function.

    Refer to the following DAX:

    Measure =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Year] ),
        'Table'[Value] <= MAX ( 'Table'[Value] )
    )
)

    Here is the result.

     

    • GuestUser's avatar
      GuestUser
      Helper V
      6 years ago

      Hi v-eachen-msft 

       

      Thanks !!

       

      The Item column is from different table and Value Column is from different table like

       

      Item Dimesnion - Item 

      Fact - Value

      Date Dimesnion -- Year

       

      Tried this

       

      Measure =
      CALCULATE (
      SUM ( 'Fact'[Value] ),
      FILTER (
      ALLEXCEPT ( 'Fact', 'Date'[Year] ),
      'Fact'[Value] <= MAX ( 'Fact'[Value] )
      )
      )

       

      But it didnt work as expected...any suggestions pls

       

      • v-eachen-msft's avatar
        v-eachen-msft
        Community Support
        6 years ago

        Hi GuestUser ,

         

        Could you please share your sample data here if you don't have any Confidential Information? Please upload your files to OneDrive for Business and share the link here.

         

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi GuestUser 

     

    Would you mind to:

    • Create a data sample 
    • Provide an expected result based on that sample
    • Provide the DAX expression that you have tried

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn