Forum Discussion

GuestUser's avatar
GuestUser
Icon for Helper V rankHelper 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
Icon for Helper V rankHelper V
6 years ago

Hi mwegener ,

 

1) Cumulative = calculate( table[trx count],

filter

(allselected(item[item_name]),

item[item_name]<=if(hasonevalue(item[item_name]),values(item[item_name])

))

 

2) Trx Count = sum(table.item_count)

 

mwegener's avatar
mwegener
Icon for Most Valuable Professional rankMost Valuable Professional
6 years ago

Hi GuestUser ,

 

I don't understand why the "cumulative count" for some items in screenshot is 0.

Here I would expect the value of the previous item.

 

  • mwegener's avatar
    mwegener
    Icon for Most Valuable Professional rankMost Valuable Professional
    6 years ago

    Hi GuestUser 

     

    I have created the following sample PBIX.
    Why are these rows in your screenshot 0?

     

  • GuestUser's avatar
    GuestUser
    Icon for Helper V rankHelper V
    6 years ago

    Hi mwegener ,

     

    Thanks for your inputs!!

     

    Changed formula for Trx Count 

    Trx_Count = if(sum(Table[Item_count]) = blank(),0,sum(table[item_count])) ----  Shows 0 instead of blank

    And Cumulative values show..previous value where Trx_Count is 0

     

    But still I am not able to get rid of records having Trx_Count=0 , when i apply filter..it gives incoorect cumulative value...as posted in previous screenshot