Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rkaul
Frequent Visitor

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. 

 

image.png

 

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? 
1 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 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

View solution in original post

8 REPLIES 8
danextian
Super User
Super User

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.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

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. 

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 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

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 😄

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. 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

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?

which transaction dates?






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

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. 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.