cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stanvsantvoort
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.

 

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Super User IV
Super User IV

Re: Calculate total decrease over time

@stanvsantvoort ,

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




stanvsantvoort
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
Super User IV

Re: Calculate total decrease over time

@stanvsantvoort ,

Running Total 1.PNG

 

 

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 ) 






                )
        )
   )


   )

 

 

 

 

 

Running Total.PNG

 

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 Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1),
    #"Added Conditional Column" = Table.AddColumn(#"Added Index", "Custom", each if [Index] = 0 then 0 else if [Index] > 0 then 1 else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [Index]= 0 then 0 else if #"Added Conditional Column"{[Index]-1}[Rev] > [Rev] then (#"Added Conditional Column"{[Index]-1}[Rev]-[Rev]) else 0),
    #"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"

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




stanvsantvoort
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
Super User IV

Re: Calculate total decrease over time

Hi @stanvsantvoort ,

Will have it to you within the hour.

Glad I could help.

Nathaniel

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Super User IV
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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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 Microsoft Power Platform event series.

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

Community Summit North America

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

Top Solution Authors