Create column/slicer on running totals

[MarianneElver]

Hi, 

I have searched in wain for similar problems, but in wain.

I have this table:

Titel	Investment	Return
a	100	600
b	500	2000
c	300	3000
d	150	1500
e	250	2250

I need a calculated column, or some other solution that will provide me with the running total of "Investment", here as an example named "Total investments":

Titel	Investment	Return	Total investments
a	100	600	100
b	500	2000	600
c	300	3000	900
d	150	1500	1050
e	250	2250	1300

I want to show how a bigger volume of investment creates a larger return, although there won't be a direct proportionality.

To show this, I want a slicer on "Total investments" that goes from 100 to 1300, and thus wil not stop at 500, which is the largest single investment sum.

When I move the slicer, the return will increase, like this:

Any help is highly appreciated.

Thanks,

Marianne

[MarianneElver]

Hi Miguel,

Thanks, what I needed was a column so that I can use it in a slicer.

I just didn't think of Power Query to be the place to find the solution!

Thanks again for diving into my problem!

[MFelix]

Hi @MarianneElver ,

To make the calculation for the total you can use the following formula:

Cumulative = CALCULATE(SUM('Table'[Investment]),'Table'[Titel]<= MAX('Table'[Titel]))

My question is how you are making the calculation based on the slicer can you please share some more insights on what you need.

[MarianneElver]

Hi Miguel,

Thanks for your message!

My need is to have a slicer that goes from zero to the total sum of possible investments.

If, like the example below, I choose an investment som of 993, then it is possible for me to invest Title a, b and c, totalling to 900, and then to have a total return of 5600. Note that the "Total Investment" column is manually created. It is this column that I need to create.

Your cumulative column example only goes up to the single largest investment sum, 500. The need is to be able to select a sum of up to 1300 in total, like in the slider to the left:

Please let me know if I should provide further information!

Thanks,
Marianne

[MarianneElver · Accepted solution]

Hi again,

I believe I have solved the problem.

The solution that did the trick was not a calculated column, but a Power Query column. 

Thanks to Mynda Treacy from Myonlinetraininghub for this example:

https://www.myonlinetraininghub.com/quickly-create-running-totals-in-power-query.

If, however a DAX column would be better, I am open to solutions.

Thanks,

Marianne

[MFelix]

Hi @MarianneElver ,

The option I created was a measure and not a calculated column, but if you were abble to achieve it with a column that's great.

Do you want me to get you a metric for the last calculation?