topic Re: Cumulative Sum by User not working properly in DAX Commands and Tips

Cumulative Sum by User not working properly

Anonymous — Wed, 14 Aug 2024 14:29:19 GMT

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!

Re: Cumulative Sum by User not working properly

Greg_Deckler — Wed, 14 Aug 2024 14:53:23 GMT

Anonymous Try Better Running Total. Better Running Total - Microsoft Fabric Community

Re: Cumulative Sum by User not working properly

Anonymous — Fri, 16 Aug 2024 05:32:11 GMT

Hi Anonymous , 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.

Re: Cumulative Sum by User not working properly

Anonymous — Fri, 16 Aug 2024 10:07:47 GMT

Hi Anonymous, 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!

Re: Cumulative Sum by User not working properly

Anonymous — Fri, 16 Aug 2024 10:08:37 GMT

hi Greg_Deckler, thank you for replying!

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

thank you so much!

Re: Cumulative Sum by User not working properly

Anonymous — Fri, 16 Aug 2024 10:43:00 GMT

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