cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper III

Create column/slicer on running totals

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

1 ACCEPTED SOLUTION
Helper III

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:

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

Thanks,

Marianne

5 REPLIES 5
Helper III

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!

Super User

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.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper III

Hi Miguel,

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

Helper III

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:

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

Thanks,

Marianne

Super User

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?

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors