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] )
    )
)

     

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.