How to get add a cumulative column starting with highest value.

[Anonymous]

Hello. I need help in making a column with cumulative value from a different column. See below for my sample data. I already tried some being suggested online, but non works for my set of data.

[Anonymous]

I built a sample dataset with small numbers to make checking it easier, I believe the following meets your requirements:

Raw measure code:

Cumulative Total =

VAR _cur_val = CALCULATE(SUM(Data[Value]), Data[ID] = Data[ID])

VAR _cum_val = CALCULATE(SUM(Data[Value]), FILTER(ALL(Data),

                                                Data[Value] <= _cur_val

)

)

RETURN

_cum_val                

Hope this helps, let me know if you have any issues or further questions.

[Anonymous]

Realise I slightly misread the problem, should be >= instead of <= as below:

[Anonymous]

It works well, thank you! I now have a column with the cumulative value starting from highest value.

My new problem is when I use the columns in my line axis visual, it automatically transforms to count and not the actual value. See below.

How do I fix this to show the actual value instead of count?

[Anonymous · Accepted solution]

That looks like a line graph that you have there. If you have one row per ID you could use the below:

Raw measure code:

Single Value = SELECTEDVALUE(Data[Cumulative Total])

I think aggregation would be more suitable, you can either click the dropdown on the column and change it to SUM, this would effectively return the sum of all the values next to whatever is on your x-axis (presumably ID, but could be anything else you have in your table). This will still work even if there is only one value, you could also create a measure to find the sum of the column and use that instead, any of these should work for you:

You could even just create the cumulative total as a measure and use that on your graph:

Raw measure code:

Cumulative Total (measure) =

CALCULATE(SUM(Data[Value]), FILTER(ALL(Data),

                                Data[Value] >= SUM(Data[Value])

)

)

Hope this helps, let me know how you get on.

[Anonymous]

This was the same measure formula I used before and guess where I went wrong?
I used <= instead of >=

Thank you very much! This was the solution I'm looking for.