topic Re: Cumulative sum without date or rank in DAX Commands and Tips

Cumulative sum without date or rank

Anonymous — Mon, 30 Dec 2019 06:38:57 GMT

Hello,
I have a dataframe like below;

I want to calculate running total for 'Duration' column(ascending version)
Do I have to rank them first or is there any other way?

All ranking examples work for columns but I need to rank Duration column as measure if needed.

Thanks in advance,

Re: Cumulative sum without date or rank

az38 — Mon, 30 Dec 2019 06:54:48 GMT

Hi Anonymous

try a calculated column

Column = calculate(sum(Table1[Duration]);filter(all(Table1);Table1[Duration]<=earlier(Table1[Duration])))

do not hesitate to give a kudo to useful posts and mark solutions as solution

Re: Cumulative sum without date or rank

Anonymous — Mon, 30 Dec 2019 07:19:12 GMT

Hello,

Thank you for your answer but I need measure because I have several slicers so every running sum calculation needs to be remade for every selection.

Re: Cumulative sum without date or rank

az38 — Mon, 30 Dec 2019 07:51:12 GMT

Anonymous

no problem

this measure should work

Measure = calculate(sum(Table1[Duration]);filter(all(Table1);'Table1'[Duration]<=SELECTEDVALUE('Table1'[Duration])))

do not hesitate to give a kudo to useful posts and mark solutions as solution

Re: Cumulative sum without date or rank

Anonymous — Mon, 30 Dec 2019 08:10:15 GMT

az38

Thanks for answer but this formula effects and consider all table.
I want to calculate it dynamically.
In below image it works fine.
https://ibb.co/qW9X0SX
But when I slice Ma_2 it doesn't re-calculate the selection
https://ibb.co/2PRS38m

Re: Cumulative sum without date or rank

Mariusz — Mon, 30 Dec 2019 09:12:49 GMT

Hi Anonymous

Try this Measure

Measure = VAR __maxDuration = MAX( 'Table'[Duration] ) RETURN CALCULATE( SUM( 'Table'[Duration] ), ALLSELECTED( 'Table' ), 'Table'[Duration] <= __maxDuration )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Re: Cumulative sum without date or rank

Anonymous — Mon, 30 Dec 2019 11:22:41 GMT

Mariusz Thank you it works, but when they are repetitive values it doesn't sum up until value changes.

For example;
Value Cumulative.Sum
35 35
17 52
15 67
11 78
7 92
7 92
3 104

3 104
3 104
3 104
2 106

How can I convert first 92 to 85?

Re: Cumulative sum without date or rank

az38 — Mon, 30 Dec 2019 11:45:10 GMT

Anonymous

what's rule do you want to define whats 7 is the first, whats is the second?

its impossible do correct without any additional dimension

do not hesitate to give a kudo to useful posts and mark solutions as solution

Re: Cumulative sum without date or rank

Anonymous — Mon, 30 Dec 2019 11:55:51 GMT

Hello az38

I added the correct version of what I want.
Is it impossible?

For example;
Value Cumulative.Sum(Wrong) Cumulative.Sum(Correct)
35 35 35
17 52    52
15 67   67
11 78    78
7 92 85
7 92 92
3 104 95

3 104 98
3 104 101
3 104 104
2 106 106

Re: Cumulative sum without date or rank

az38 — Mon, 30 Dec 2019 16:51:55 GMT

Anonymous

I dont see a way to split 7's values in your data model without addition any other order column

do not hesitate to give a kudo to useful posts and mark solutions as solution