Cumulative total issue for 0 count

[rkaul]

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? 

[danextian]

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.

[rkaul]

Here is the sample data 

https://drive.google.com/file/d/1wMk6MORmKAb2HAPZPeM8diuHVyZJhEE9/view?usp=sharing

When I apply the transaction date filter, it creates an issue. 

[danextian · Accepted solution]

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 

[rkaul]

I just deleted the original visual and redid it and it worked for some weird reason. I dont understand PBI many times, haha. Thanks a lot for your help 😄

[danextian]

Hi @rkaul ,

Haha. I am actually not sure what you are trying to achieve. I am a bit confused so I modified the pbix a bit and created two additional measures. 

[rkaul]

I think you are getting the same error in your PBi also. if you look at weeks 21, 21, 23, 27 (lag) in the cumulative week column, its giving a weird value. What is your opinion on that?

[danextian]

which transaction dates?

[rkaul]

yes, i'm using an external date (calendar) table inplace of transaction date, you're right. From your PBI, it looks like transaction date works fine, but calendar date slicer (connected to the transaction date) doesn't seem to work. Is there a resolution to that? sorry i'm a bit new to PBI.