Cumulative Sum by User not working properly

[jacqueshervo]

Hi there! 

I want to look at the cumulative amount of savings ([savings]) by user ([external_payment_id]) over time ([event_month]).

It's working for 1 or 2 specific users when I filter the plot on the user's id.

However, the cumulative sum doesn't make sense at all when I select all users. 

I don't understand why it's working for a couple of random users, and not all. 

Do you guys have an idea?

Here is the code below as well as the plots shows on PowerBI

CumulativeSavings =

CALCULATE(

SUM('monthly_user_metrics'[savings]),

FILTER(

ALLEXCEPT('monthly_user_metrics', 'monthly_user_metrics'[external_payment_id]),

'monthly_user_metrics'[event_month] <= MAX('monthly_user_metrics'[event_month])

)

)

Thank you so much for your help!

[Greg_Deckler · Accepted solution]

@jacqueshervo Try Better Running Total. Better Running Total - Microsoft Fabric Community

[v-yajiewan-msft]

Hi @jacqueshervo , Greg_Deckler, thank you for your prompt reply!

I did a sample test based on your description, but was unable to reproduce your issue.

Please check my test pbix to see if I missed anything, and if possible you can also upload your example file for better testing.

Thank you for your understanding!

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jacqueshervo · Accepted solution]

Hi @v-yajiewan-msft, thank you so much for taking the time to test the issue on your own! I believe that there are some issues when I take into acconts a lot of users or some weird users I have in my database.

it's good to know that I got the right way though! 

thank you so much! 

[Greg_Deckler · Accepted solution]

@jacqueshervo Try Better Running Total. Better Running Total - Microsoft Fabric Community

[jacqueshervo]

hey @Greg_Deckler, I tried the running total, but it looks like it doesn't cumulate sum properly. 

here is the code I used to get the following chart:

RunningTotalSavings2bis =

CALCULATE(

DIVIDE(SUM(monthly_user_metrics[savings]), DISTINCTCOUNT(monthly_user_metrics[external_payment_id]),0),

FILTER(

ALL('monthly_user_metrics'[mss]),

'monthly_user_metrics'[mss] <= MAX('monthly_user_metrics'[mss])

)

)

do you have an idea why?

thank you so much!

Jacques

[jacqueshervo]

hi @Greg_Deckler, thank you for replying!

I've tried your solution and it looks like it's working properly.

thank you so much!