Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Hi @Anonymous , 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
Nathaniel_C
Super User
Super User

@Anonymous ,

 

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!




Anonymous
Not applicable

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. 🙂

@Anonymous ,

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!




Anonymous
Not applicable

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.

Hi @Anonymous ,

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!




Hi @Anonymous , 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!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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