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

 

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

Hi GuestUser ,

 

I am glad that your problem has been resolved, you could accept your own reply to close the case.

 

  • mwegener's avatar
    mwegener
    Most 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
    Helper V
    6 years ago

    hi,

     

    facing some strange behaviour

     

    the below formula works

    Cumulative = calculate( table[total sales],

    filter

    (allselected(item[item_name]),

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

    ))

     

    when i dont put any filter condition on total sales..cumulative value is correct...but it shows item with no sales as well

     

    So when i put filter condition as total sales is not blank in report ,

    then the cumulative value shows wrong value..it shows correct for 2 records..then the third record..it shows the same value as total sales

     

    Any suggestions pls?

     

     

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    6 years ago

    Hi GuestUser,

     

    can you provide a screenshot?

     

  • GuestUser's avatar
    GuestUser
    Helper V
    6 years ago

  • GuestUser's avatar
    GuestUser
    Helper V
    6 years ago

    Hi mwegener

     

    First screenshot is without any filter condition added -- it giving correct result

     

    Second one - Added filter condition on filter pane ..like trx count is not blank...it gives incorrect result

     

    basically i do not need those items in the report with trx count blank

     

    Any suggestions pls

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    6 years ago

    Hi GuestUser ,

     

    can you post the DAX formula for both measures?

     

  • GuestUser's avatar
    GuestUser
    Helper V
    6 years ago

    Hi,

     

    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)

     

  • GuestUser's avatar
    GuestUser
    Helper 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
    Most 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.

     

  • GuestUser's avatar
    GuestUser
    Helper 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