cancel
Showing results for
Did you mean:
Regular Visitor

## Calculate total decrease over time

Dear all,

I would like to calculate the total decrease of D202_SE01_RPM.Value over time, like the red line in the 2nd graph.

How can I easily do this by using measures?

So the increase parts are not calculated. Only the decrease parts of 'value' are being added up with each other by every decrease.

Kind regards,
Stan van Santvoort.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV

## Re: Calculate total decrease over time

Hi @stanvsantvoort , Below is the data that I used.

Min	Rev
1	300
2	400
3	200
4	150
5	600
6	300

Here is my pbix file. pbix file  Not sure that you even need to create a spreadsheet unless you try to refresh.

Let me know if this works for you. You can open this and change data source settings to a new spreadsheet after you build it and save it.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Proud to be a Super User!

6 REPLIES 6
Super User IV

## Re: Calculate total decrease over time

Not sure what you are trying to get here? Would you give us an example of the expected output?

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Proud to be a Super User!

Regular Visitor

## Re: Calculate total decrease over time

Hi @Nathaniel_C ,

First of all, thanks for you reaction!
I will now give a better descreption of my expected output.

I got a value that changes over time. The value can increase or decrease.
I am not interested in the increase of the value, I want an output that only sums up all the decreases.

So every time the value decreases, I want to add that up to a total over time.

So minute 1 the value is 300.
Minute 2 the value increases to 400.
Minute 3 the value decreases to 200.

Minute 4 the value decreases to 150.

Minute 5 the value increases to 600.
Minute 6 the value decreases to 300.

As output I want to have a graph that adds up all the decreases over time.
The graph will be 0 at minute 1, than it increases to (400 - 200 = 200) on minute 3.
On minute 4 it will be (200 - 150 + (last value = 200) = 250) on minute 4.
On minute 5 it will stay 250 because there is no decrease.
On minute 6 it will be (600 - 300 + (last value = 250) = 550).

Hope this helps. 🙂

Super User IV

## Re: Calculate total decrease over time

Ah the sweet smell of success! So I went down various paths, which mostly consisted of trying to get the row above recognized, which works except not for running total. So I did this in both Power Query, and Power BI. Used your data example, Table = RPM.

A lot of work, but as usual learned a lot! Let me know if you have any questions, and thanks for posing the question.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

The measure works as a visual, or as a column.

Running Total = CALCULATE(SUM(RPM[Calc]), FILTER(ALL(RPM), RPM[Index] <= MAX(RPM[Index])))

RPM[Rev Total] which is the same as RPM[Calc], is done in Power BI as a column.

Rev total =
VAR calc1 =
IF([Index]=0,0 ,
If (CALCULATE (
MAX ( [Rev] ),
FILTER ( RPM, [Index] = ( EARLIER ( [Index] ) - 1 ) )
)
-   CALCULATE(

MAX ( [Rev] ),
FILTER ( RPM, [Index] = ( EARLIER ( [Index] ) - 0 )
)
)

<0,0,

CALCULATE (
MAX ( [Rev] ),
FILTER ( RPM, [Index] = ( EARLIER ( [Index] ) - 1 ) )
)
- CALCULATE(

MAX ( [Rev] ),
FILTER ( RPM, [Index] = ( EARLIER ( [Index] ) - 0 )

)
)
)

)

Finally the Advanced Editor in Power Query to originally set up the columns through RPM[Calc]

let
Source = Excel.Workbook(File.Contents("C:\Users\nat_c\OneDrive\Desktop\revs.xlsx"), null, true),
D202_SE01_RPM_Table = Source{[Item="D202_SE01_RPM",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(D202_SE01_RPM_Table,{{"Min", Int64.Type}, {"Rev", Int64.Type}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Min", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Added Index", "Custom", each if [Index] = 0 then 0 else if [Index] > 0 then 1 else null),
#"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column1",{{"Custom.1", "Calc"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Calc", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Custom"})
in
#"Removed Columns"

Proud to be a Super User!

Regular Visitor

## Re: Calculate total decrease over time

Hi Nathaniel,

Thanks for your help! I really appreciate it .

But now I am trying to understand the code you have written.
Could you maybe upload your excel file revs.xlsx? Hope I understand then how you did it.

Because the part of how the CALC tables are made via Advanced Editor is where I'm struggling with. Because my data comes out of a live database. So when I see how you did it with your Excel Sheet, I hope to understand how I can make this with my database. 🙂

Kind regards,
Stan van Santvoort.

Super User IV

## Re: Calculate total decrease over time

Will have it to you within the hour.

Nathaniel

Proud to be a Super User!

Super User IV

## Re: Calculate total decrease over time

Hi @stanvsantvoort , Below is the data that I used.

Min	Rev
1	300
2	400
3	200
4	150
5	600
6	300

Here is my pbix file. pbix file  Not sure that you even need to create a spreadsheet unless you try to refresh.

Let me know if this works for you. You can open this and change data source settings to a new spreadsheet after you build it and save it.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Proud to be a Super User!

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors