Forum Discussion

rkaul's avatar
rkaul
Frequent Visitor
6 years ago
Solved

Cumulative total issue for 0 count

Hello all,

 

I'm trying to calculate 'cumulative chargebacks' by lag in weeks, however, for those weeks where the chargebacks are 0 or blank, the cumulative chargebacks show a weird value. e.g. if you see for Lag (weeks) = 28, the cumulative chargebacks number is showing a weird number. 

 

 

Im using the following measure for running total - 

 

Cumulative Chargebacks Number = CALCULATE([Number of Chargebacks],FILTER(ALLSELECTED(CB_report_Chargeback_data[Lag (weeks) (bins)]),CB_report_Chargeback_data[Lag (weeks) (bins)]<=MAX(CB_report_Chargeback_data[Lag (weeks) (bins)])))
 
The problem only arises when im using an external date slicer on the above table ( to view the table in a specific date range). Its calculating the cumulative total accurately for chargebacks which have non-zero value, but when chargebacks are blank/zero, cumulative totals seem messed up. Can anyone help on this? 
Need Help
  • danextian's avatar
    danextian
    6 years ago

    hello rkaul ,

     

    I created a measure using ALL instead of ALLSELECTED and the result seems fine

     

    Cumulative Sum = 
CALCULATE (
    SUM ( Sheet2[Chargeback Amount] ),
    FILTER (
        ALL ( Sheet2[Lag (weeks)] ),
        Sheet2[Lag (weeks)] <= MAX ( Sheet2[Lag (weeks)] )
    )
)

     

     

    Going back to your original post, what do you mean by sing an external date slicer? Are you using a separate dates table? 

    Anyway, here's the pbix based on your data: https://drive.google.com/file/d/1xc8F0qtTA_ydAMHetfyO0O58-6vyeVZG/view?usp=sharing 

8 Replies

  • danextian's avatar
    danextian
    Super User
    6 years ago

    hI rkaul ,

     

    Try changing your formula from ALLSELECTED to just ALL.

    Cumulative Chargebacks Number =
CALCULATE (
    [Number of Chargebacks],
    FILTER (
        ALL ( CB_report_Chargeback_data[Lag (weeks) (bins)] ),
        CB_report_Chargeback_data[Lag (weeks) (bins)]
            <= MAX ( CB_report_Chargeback_data[Lag (weeks) (bins)] )
    )
)

    Otherwise, please post a sample anonimized data.